Structured Query Language is a language for interacting with relational databases. Read the tag wiki's guidelines for requesting SQL reviews: 1) Provide context, 2) Include the schema, 3) If asking about performance, include indexes and the output of EXPLAIN SELECT.
3
votes
1answer
20 views
Retrieving MAC addresses based on PCI interface connections and SQL queries
I am currently using this C# method to retrieve MAC addresses from a user's computer as a sort of unique identification. It retrieves the MAC address based on the fact that the physical card is ...
0
votes
0answers
12 views
Whats wrong with this MDX query? [on hold]
I'm trying to the MDX query below :-
WITH
MEMBER [Measures].[OrgName] AS
[Organisation].[LEVEL8].CURRENTMEMBER.Properties("Name")
SELECT ...
3
votes
0answers
21 views
Database Migrations
I'm starting to learn Clojure, and would like feedback on some code I wrote to manage database migrations. Any recommendations to make it more robust, efficient, idiomatic, elegant, etc... are ...
1
vote
2answers
30 views
Exposing WCF Service with dynamic search operation
I have this WCF service providing search functionality - to be used by our client in his web application. There can be any number of search parameters and possible nesting of AND/OR operations.
Could ...
5
votes
1answer
39 views
Inserting text file rows into an Access DB - Optimizing
How can I improve my inserts?
I am trying to speed up the process of inserting all of the rows in my text file into an Access DB. I originally switched to this route because of the size of those text ...
5
votes
2answers
53 views
How to properly call an “upsert” using parameterized raw SQL to Postgresql in ActiveRecord?
I need to call an upsert to my Category table in PostgreSQL. My current solution uses the exec_query API in ActiveRecord, using binding.
What I got so far looks like this:
VERIFY_CATEGORY_SQL = ...
5
votes
1answer
30 views
Eliminate multiple calls to SQL IN operator?
I am working with a table where each row represents a transaction and each transaction has further descriptions in up to 4 fields. Note that I cannot change the structure of this table. I need to ...
3
votes
1answer
115 views
Using SQL with encryption
This is my first project I am doing in VB.NET, and also my first real programming project. There is sensitive data, so I am utilizing Microsoft's Encryption/Decryption class (clsCrypt).
For ...
1
vote
1answer
16 views
Excel to SQL Upload
Aim: Import Excel to SQL
Potential Issue:
Wrong file type - this is handled in the 'upload' button by not allowing anything but *.xlsx files
The wrong type of Excel file i.e. not 2003 onwards, ...
12
votes
1answer
110 views
Calculating Lost Reputation
A discussion arose not long ago on the 2nd Monitor about how much reputation has been lost due to the reputation caps. There are a number of queries on the SEDE which try to address this:
A Users ...
3
votes
0answers
54 views
System for inputting and monitoring worker shifts
I've been building a system for inputting and monitoring shifts for casual staff, who work across multiple sites with the ability to generate accounting information.
I've had some help from Stack ...
2
votes
1answer
103 views
Optimize MySQL in a stored procedure
How should I optimize my code for better performance? When I execute the code outside of MySQL stored proc it is 500% faster.
MySQL stored procedure
SELECT bs.business_id, adr.street, bs.`name`, ...
-6
votes
1answer
60 views
Stored Procedure logically or coding
I have this SP that I'd like improved. I've changed variable names for some reason. I am working on SQL Server 2000.
USE [DBName]
ALTER PROCEDURE [dbo].[Table_GetSomething]
@name ...
6
votes
1answer
73 views
Can you replace a REPLACE statement, or 9?
I would really like something to replace all of these REPLACE Statements.
SELECT DISTINCT
CAH.CaseNbr
, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
...
9
votes
3answers
120 views
SEDE Top Sponsors
I wanted to see the site's top sponsors - users that have paid bounties on questions that they didn't own.
I started off with a bounty-related existing query, selected the details into a subquery, ...
7
votes
2answers
41 views
SE Data Explorer Query: Average score for questions and answers, by tag
Being curious to see which tags on this site get the most attention, I developed a Stack Exchange Data Explorer query:
WITH CanonicalTags AS (
SELECT Id, TagName, Id AS AliasId, TagName AS Alias
...
2
votes
1answer
19 views
SQL Look up in a Stored Procedure across three tables
The idea of the below code is that I feed back to a Gridview if the user has permission to view the property otherwise that property is not show.
The data is passed in via SessionParameters
USE ...
3
votes
1answer
44 views
Small query - removing XML tags
I have some code that I wrote almost a year ago exactly (1/9/2013) and I would like to know if I wrote it well or if it can be improved. I don't have any fun input or output, as these are not set ...
4
votes
1answer
59 views
SQL Join code check and peer review
I am still fairly green when it comes to SQL joins. The below code works but I want to check that I am doing it the best way before I copy and paste errors into other work in the future.
The idea of ...
4
votes
1answer
78 views
Make this MySQL query more elegant &/or efficient
I have created a MySQL query that works, but I feel there must be a better way. The query will be used by a PHP script whose purpose is to assign conditions & subconditions to new participants in ...
2
votes
1answer
123 views
Increase security of sign up form code
Does my PHP look secure enough for a sign up form?
<?php
$con=mysqli_connect("host","user","password","db_name");
$sql="INSERT INTO users values (?, ?, ?)";
if ($stmt = mysqli_prepare($con,$sql)) ...
2
votes
1answer
110 views
Converting entire table to JSON data. Is there a better way?
Building a 'card' database: I'm simply learning to take input data and store to database. Incorporating JSON, PDO, SQL, and enforcing my general coding skills through PHP, hopefully.
$query = ...
2
votes
1answer
60 views
Normalization: is it done well? [closed]
I am creating game review database. I am new at this, but I am trying my best. Little bit about database: The system is simple, the user will fill out a form, where they will insert their name, email ...
2
votes
2answers
64 views
Does this query affect perfomance?
I have this query:
SELECT * FROM
(SELECT u.id,u.email,u.verified,u.verified_on,u.created_on,ca.html AS age,cg.html AS gender,cs.html AS state
FROM users u
LEFT JOIN combo ca ON ca.combo_group='age' ...
6
votes
2answers
118 views
Local user registration
More javascript (nodejs) to go with the passport wrapper I just posted:
UserBook.js
/*
* A wrapper for user Registration for a web site.
* This is an MYSQL version of the client to provide ...
2
votes
3answers
295 views
Convert Sql LIKE to Regex
I have a bit of code that converts a Sql Like expression to a regex expression for the purposes of a Linq to objects Like extension method. For some time I have been using this conversion.
This ...
3
votes
2answers
68 views
Optimized Query
I am having a structure of 3 tables
Table mintemp consist of matcode,min_qty,jo_no,mr_no
Table min_out_body consist of matcode,out_qty,jo_no,mr_no
Table eu_min_out_body consist of ...
4
votes
2answers
77 views
SQL query where id=… or id=… or id=… etc
I haven't had much experience with postgresql (none) and I am wondering/hoping that there is a better way for me to do this query.
SELECT * FROM member_copy WHERE id = 17579 OR id = 17580 OR id = ...
4
votes
2answers
177 views
C# with Oracle ODP.NET for Review
What can be improved im my code:
using (var oracleConnection = new OracleConnection(ConnectionString))
{
using (var oracleCommand = oracleConnection.CreateCommand())
...
4
votes
3answers
79 views
Remove duplication in SELECT statement
Say I have the following SQL:
SELECT
amount,
amount*.1,
(amount*1)+3,
((amount*1)+3)/2,
(((amount*1)+3)/2)+37
FROM table
Instead of repeating that identical code every time, I ...
5
votes
2answers
116 views
Is there a way to shorten and/or simplify this Select Statement
I have the following MySQL SELECT statement. It is working fine except that the code is too long. I have been looking throughout the Internet to figure out how I can make it shorter.
SELECT regd, ...
4
votes
3answers
205 views
Speed up application and avoid SQL Timeouts
The code works just fine, but recently performace has taken a hit and it has SQL Timeouts far too often. I have it set up so I can run 50 different version of the application two for each different ...
3
votes
1answer
121 views
Improve speed of LINQ query
How can I improve the speed of this LINQ query?
using (var txn = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted ...
2
votes
2answers
69 views
Can this be made to run faster?
This is a stored procedure that takes 5-30+ minutes to run depending on the parameter they select.
It also has a nasty side effect of clogging down our SQL Server.
SET NOCOUNT ON;
DECLARE ...
3
votes
1answer
85 views
Query too slow - Optimization
I am having an issue with the following query returning results a bit too slow and I suspect I am missing something basic. My initial guess is the 'CASE' statement is taking too long to process its ...
2
votes
2answers
109 views
Injecting data into XML using a dll
This is a template for one of our data injection tokens.
When I first looked at the template, it was worse than it is now. I would like some input on this because I think that it can be cleaned up ...
2
votes
1answer
181 views
SQL database query wrapper
How can I write a better database query wrapper?
//code for connection
exports.connect = function(callback) {
client.connect(function(err, conInfo){
if (err) callback(err);
...
1
vote
1answer
46 views
My date range generator SQL code - any limitations [closed]
I made some code to generate date ranges in SQL server 2005 and above. I am not sure if there will be any problems in this. Can you find any bugs or limitations in my code ?
Declare @StartDate ...
1
vote
2answers
247 views
SQL database design e-commerce
I am new to database design but am fairly familiar with SQL and its syntax. I want to create a database for an E-commerce website that will sell a single product type such as a shirt. They could be ...
1
vote
1answer
53 views
for loop vs table cast
I've created a sample table in Oracle 11.2.0.1.0:
create table my_table ( id number , val varchar2(5));
insert into my_table
select level , dbms_random.string('a', 1) from dual
connect by level ...
6
votes
1answer
157 views
Help optimizing this query with multiple where exists
In the application I'm building, the user is able to define 'types' where each 'type' has a set of 'attributes'.
The user is able to create instances of products by defining a value for each ...
1
vote
1answer
43 views
Checking Synchronization issue in SQL?
I have the following SQL. I want to make that the SP should work under load. Means it should not have any synchronization issues.
ALTER PROCEDURE [dbo].[DeleteOldDeviceID]
(
@OldDeviceID ...
1
vote
2answers
61 views
Same records between two tables
I have the following tables:
player
| id | name | surname |
|:-----------|------------:|:------------:|
| 1001 | Mike | Tyson |
| 2001 ...
1
vote
2answers
97 views
Review single step approach, rather than multiple temp tables
Overall the intent is to consolidate individual sales transactions to total debits/credits summing up the totals by year and month--retaining a running balance from start to end. This code uses ...
3
votes
4answers
147 views
Are these two scripts secure enough from exploits?
I'll gladly appreciate it if you could review my code below and let me know if they are sufficiently secure.
My main website and these scripts will use same database, so I need to make sure they are ...
2
votes
2answers
40 views
SQL Server 2012: How do I eliminate multiple statements in this code to a more efficient, single statement?
This code completes under a second, however its seems like I've taken the "Mr. Bean" way around getting there.
The goal is to get the average number of orders processed in a single day based on how ...
3
votes
2answers
48 views
Oracle nested query
Essentially I'm attempting to select the top read (based on read_date), and check if it has a type in ( 'R','S','C','B','Q','F','I','A')
select *
from (
select *
from (
...
2
votes
1answer
63 views
is there a way to simplify this query?
SELECT * FROM
(SELECT a.*, ROWNUM rnum from
(SELECT a.CUST_FULL_NAME,a.TAX_NET,
(SELECT NEXT_INT_PAYMENT_AMOUNT_NET FROM
(SELECT a.*, ROWNUM rnum from
(SELECT ...
1
vote
2answers
126 views
Async querying SQL in IIS
let reply = ref ""
let doAuto =
async {
let! resultAA =
async { return SqlCmd.aaHits query |> Async.RunSynchronously |> List.ofSeq }
let! resultBB =
...
2
votes
1answer
171 views
DAL Efficiency Help
I am attempting my first try at some c#. So far I love it, (not more than vb tho ;)) however, I am wondering if I can make these classes a bit more efficient.
Please note ###Test is my attempts at ...
