Results 1 to 6 of 6
Thread: Optimizing SQL Queries !

072813, 23:17 #1Registered User
 Join Date
 Apr 2013
 Posts
 41
Unanswered: Optimizing SQL Queries !
I'm running the following query and it takes lot of time to execute a large data of 700MB size. Is there any way I can get my query run faster?
Also, could you help me in optimizing my query.
Here is my query:
I have written six qyeries in a coldfusion file. Didn't mentione any coldfusion tag since it's a pure db forum. Can I somehow make the following 6 queries
efficient? Please explain. Thanks
Code:SELECT (select count(*) FROM MyDatabase) AS TOTAL_CONNECTIONS, (SELECT count(*) FROM MyDatabase WHERE EVENTS = "FIRST") AS FIRST_CONNECTIONS, (ROUND((SELECT FIRST_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "FIRST" ; SELECT (select count(*) from MyDatabase) AS TOTAL_CONNECTIONS, (SELECT count(*) from MyDatabase where EVENTS = "SECOND") AS SECOND_CONNECTIONS, (ROUND((SELECT SECOND_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "SECOND" ; SELECT (select count(*) from MyDatabase) AS TOTAL_CONNECTIONS, (SELECT count(*) from MyDatabase where EVENTS = "THIRD") AS THIRD_CONNECTIONS, (ROUND((SELECT THIRD_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "THIRD" ; SELECT (select count(*) from MyDatabase) AS TOTAL_CONNECTIONS, (SELECT count(*) from MyDatabase where EVENTS = "FOURTH") AS FOURTH_CONNECTIONS, (ROUND((SELECT FOURTH_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "FOURTH" ; SELECT (select count(*) from MyDatabase) AS TOTAL_CONNECTIONS, (SELECT count(*) from MyDatabase where EVENTS = "FIFTH") AS FIFTH_CONNECTIONS, (ROUND((SELECT FIFTH_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "FIFTH" ; SELECT (select count(*) from MyDatabase) AS TOTAL_CONNECTIONS, (SELECT count(*) from MyDatabase where EVENTS = "SIXTH") AS SIXTH_CONNECTIONS, (ROUND((SELECT SIXTH_CONNECTIONS / (TOTAL_CONNECTIONS))*100)) AS "SIXTH" ;

072913, 08:07 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Without having your database to try against, I just threw together some "air code" but I think that it is on the right track:
Code:SELECT Count(*) AS TOTAL_CONNECTION , Sum(CASE WHEN 'FIRST' = EVENTS THEN 100 END) / Count(*) AS FIRST , Sum(CASE WHEN 'SECOND' = EVENTS THEN 100 END) / Count(*) AS SECOND , Sum(CASE WHEN 'THIRD' = EVENTS THEN 100 END) / Count(*) AS THIRD , Sum(CASE WHEN 'FOURTH' = EVENTS THEN 100 END) / Count(*) AS FOURTH , Sum(CASE WHEN 'FIFTH' = EVENTS THEN 100 END) / Count(*) AS FIFTH , Sum(CASE WHEN 'SIXTH' = EVENTS THEN 100 END) / Count(*) AS SIXTH FROM MyDatabase
In theory, theory and practice are identical. In practice, theory and practice are unrelated.

072913, 18:43 #3Registered User
 Join Date
 Apr 2013
 Posts
 41

072913, 18:48 #4Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Exactly, the constant doesn't really matter to the Sum() function and using 100 instead of 1 allows you to avoid one additional mathematical operation. The saved operation is trivial, but saving one more step where datatypes can cause problems is important.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

073013, 00:49 #5Registered User
 Join Date
 Apr 2013
 Posts
 41

073013, 01:09 #6Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54I'd expect your original query to take around three minutes on stock hardware (a VM run up for use primarily as a web server). I'd expect the query that I provided to run in roughly 40 seconds on a similarly configured machine.
From a logical perspective, you might gain some performance by indexing the EVENT column, especially if there are rows that are not of interest for your calculations.
From a physical perspective, I need more insight to help you. Do you have access to the performance statistics for the machine while your query is running? Look to see what is taking the most time, and focus your attention on that.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.