Results 1 to 6 of 6
  1. #1
    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" ;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without 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
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    41
    Quote Originally Posted by Pat Phelan View Post
    Without 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
    -PatP
    Thanks for your reply. Why did you mention 100 after THEN clause? I have seen sometime people mentioning 1 as well after THEN clause. I suppose here for the sake of calculating percentage you have used it, Am I right?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Exactly, 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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2013
    Posts
    41
    Quote Originally Posted by Pat Phelan View Post
    Exactly, 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.

    -PatP
    Thanks. One more question, I'm querying 1 GB of data with these queries and it take around 2-4 minutes to retrieve the results. What is the best way to speed up my query results?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •