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.

learn more… | top users | synonyms

1
vote
3answers
74 views

Making a new column and setting default values equal to the COUNT of a certain condition in SQL? [on hold]

I have 2 tables. (table1, table2) Now, I would like to do something along the lines of: ...
3
votes
0answers
20 views

sql indexing and performance [migrated]

I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities. There are 8 columns in this ...
3
votes
0answers
27 views

Tuning a query for performance

I am running this query and it takes several minutes to complete, how can I tune it (unfortunately I don't have the privileges to run an SQL tuning advisor) : ...
1
vote
0answers
25 views

Length limited listagg for muliple m:n relations in a view

I have a few views that all follow the same pattern. They select a primary key from some main table and then a few semicolon seperated strings of captions of related rows over many-to-many relations. ...
7
votes
1answer
51 views

A faster way to combine two SQL table

I would like to give it a try and ask about my codes on here from now on to see if there are improvement for'em this code is supposed to combine two sql tables into one. ...
7
votes
4answers
489 views

Bad practices in this procedure to calculate player statistics?

I recently self-taught myself MySQL as quickly as possible because it was needed for a project that I'm working on. Now that I've got some time I've been working on improving some thrown together SQL ...
2
votes
2answers
38 views

Submit form data to MYSQL table with PHP execute

I have written code to send a simple email submission form to a sql table so that I can manage the data. I would like feedback on whether or not the code that I have written is efficient and secure. I ...
4
votes
1answer
31 views

SEDE query to find users with exactly one post in a particular tag

I'm doing some research for a meta post on Gardening.SE (where I'm a pro tempore moderator). I'm using the Stack Exchange Data Explorer (SEDE) to find information about first time users who post ...
0
votes
1answer
29 views

SQL double nested query

This query produces the results I want. Is there a less wordy or more effective way to write this query? ...
3
votes
1answer
32 views

Materializing any ADODB Query

Following-up on Creating ADODB Parameters on the fly and pushing the "wrapping" of ADODB a step further, I have written two more classes that allows me to expose methods that don't require a ...
3
votes
1answer
34 views

Making VBA generated dynamic SQL statements safe against injection

I need to use SQL extensively in VBA macros that I write. Since the DB is from our enterprise application, I use vendor's library for this. I generate dynamic queries, sometimes with many ...
2
votes
0answers
31 views

Optimizing code for querying multiple related records in Rails

I have an Account, User and Group model. All User and ...
2
votes
1answer
39 views

The use of database “sets” rather than “functions”

I've currently got a MSSQL stored procedure that uses multiple SQL functions to achieve a number of returns. Now I've been told about using sets rather than functions as it creates " bad code smell of ...
3
votes
1answer
73 views

Loading, computation and writing 500.000 rows in database

first let me explain what I am trying to achieve here : I am supposed to compute some results, using data from several tables, filtered by a run_id. One of these tables has about 500.000 rows per ...
0
votes
1answer
56 views

How can I modify this query for performance?

This is the stored procedure I'm using to list some places in my website. How can I optimize it to perform well? Which functions in query is taking much time for its execution? ...
1
vote
1answer
48 views

Selecting from a database into a DataGridView

I have a piece of code which grabs data from an SQL database and displays it in a DataGridView. This code works perfectly fine for me. It has been pointed out to me that the below code is bad ...
4
votes
2answers
360 views

Is there a PHP security exploit with $_POST in my code?

I posted this question here. And an answer stated that I should not do: $table_name = 'survey_'.$_POST['surveyid']; because It is easy for a hacker to ...
4
votes
3answers
208 views

Generic class to call table parametered stored procedures

This code is to allow calling any stored procedure with one user defined table as the parameter. I usually use Entity Framework, but I just don't need it for my current solution, so I am rolling my ...
5
votes
2answers
48 views

SQL Query that pulls rows in current table based on parent id optimization

I am creating a user permission system. I will have my user permissions set up off of "objects" and not pages. For example, I have an EVENTS section. Within that ...
2
votes
2answers
38 views

More concise and more efficient way of finding the value in SQL (PL/SQL) Stored Proc

I have a PL/SQL Stored Procedure which searches for a row in 3 different tables (not all columns are the same). The logic goes as follows: ...
2
votes
1answer
54 views

Two script completing the same task, which is the best way?

I have the following two methods of completing the same task, however I'd like to know which is the preferred method and WHY? Or if there are any suggestions to complete the task in an even better, ...
5
votes
1answer
57 views

Inefficient query actually more efficient?

I have two queries; one runs significantly faster than the other, but the faster one is the one that I would have bet money on to be less efficient. These are both being executed through a front end ...
10
votes
2answers
125 views

Another way to run reports on a SpiceWorks SQLite database using a linked server in SSMS?

I created a linked server in SSMS so that I could run reports on a SpiceWorks SQLite database. there are a few quirks in the whole system. These simple queries are becoming some of the most ...
7
votes
1answer
117 views

ActiveRecord extension to find next / previous record(s) in one query

I just wrote this gem order_query to find next or previous records relative to the record assuming a (possibly complex) ORDER BY over the records. For example, this ...
6
votes
1answer
42 views

Aggregating Conditional Sums

I have recently forked/rewritten a SEDE query (here) that aims at figuring out where a site stands in terms of avid users and distribution of reputation scores, compared to a specific target (number ...
7
votes
3answers
137 views

Return the top 5 of each kind of record

I have this currently working code, but it's extremely slow. I'm talking almost an hour to execute, if it executes at all (our server isn't all that great to start with). Is there a better way to ...
6
votes
1answer
52 views

Removing image records if no physical file exists

I have a working script that selects image fields in all tables and empty their values if the physical file doesnt exist. ...
10
votes
2answers
88 views

Comparing client lists with Cross Joins

I've written a query to compare the clients in our database with the people in a list that I've received. It needs to check if anyone from the list is one of our clients. I've created a temporary ...
3
votes
1answer
31 views

Finding users with the highest Following vs. Friends ratio

I am trying to find the users who have the highest "Following vs. Friends" ratio in my Twitter timeline. I have a SQLite database of the users in my Twitter timeline and each column contains data ...
2
votes
1answer
67 views

Website for updating a div

I've built a web site that uses AJAX to update a div. Most of the data displayed in the div is drawn from a MySQL database. All the pages are PHP. I've tried to follow best practices. I put all the ...
10
votes
3answers
345 views

Finding person with most medals in Olympics database

I have an Olympics database from each Olympic year and I want to find the person that has won the most medals. The main problem is that I'm basically querying the same sub-query twice in ...
5
votes
1answer
136 views

Fastest image upload into SQL database

Hello I am trying to upload an image (screenshot from desktop) to SQL database as fast as possible. I would like to optimize my current procedure in terms of speed: ...
5
votes
1answer
83 views

Query performance of a SELECT statement using WHERE and NOT IN

Can anyone help improve the performance of my query? When I run it, it locks the database for 5 to 10 minutes. ...
5
votes
2answers
56 views

More than one answer by the same user

I hacked together this little query on the Data Explorer The query finds questions where an answerer has posted two or more answers (which in itself is not necessarily a bad thing), it gives you the ...
1
vote
1answer
34 views

Performance problem: Ranking with ties and skipping rows on passmark condition

I am using the following code for calculating ranking and it is working great for not large data. But when it comes to large data, processing time takes almost a minute. Please can anyone suggest a ...
5
votes
2answers
71 views

Is it advisable to integrate SQL statements in the JSP pages that are not displayed to the user but handled at the back?

I have a signup form in which the user has to enter the email address and after some quick asynchronous processing at the backend I have to tell the user that whether the email address is already ...
3
votes
1answer
39 views

Best way to get two columns from a table

Basically I have taken over a project and found a table that has the following video and video title fields in it (why they didn't create a separate linked table is beyond me): ...
5
votes
3answers
320 views
3
votes
1answer
41 views

Check for similar value with SQL

I've two table (t1 and t2) with 3 identical integer columns : c1, c2 and c3. I want to count how many value in t1 are in t2 So I start to write : ...
5
votes
1answer
29 views

Updating number of article views - potential concurrent access issue?

I have articles on my website (built in PHP) and when an article is viewed the number of views is recorded back in the database. The SQL code snippet of my load ...
6
votes
1answer
133 views

How to fill an ArrayList of ArrayLists with a Left Join?

I have a class Employee that contains an ArrayList of Projects. I'm storing the Employees in one table, and the Projects in another. I'm trying to find the best way to create an ArrayList of ...
1
vote
1answer
59 views

build a string of ints for SQL WHERE IN [closed]

What's the best approach for creating on the fly a string of intergers for a WHERE IN clause in SQL For example@ ...
3
votes
1answer
60 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 ...
6
votes
1answer
64 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
56 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 ...
5
votes
1answer
76 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
88 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 ...
5
votes
1answer
34 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
2answers
167 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 (...
1
vote
1answer
25 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, ...