Optimized query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Javilen
    New Member
    • Sep 2007
    • 20

    #1

    Optimized query

    Hello,

    I am looking for some Query Optimization help,

    We are running a web application that is growing experientially in traffic and we are not running into Problems with Deadlocks on the SQL database.

    After analyzing the logs 90% of our deadlocks happen from the main login page(the page hit after login)

    This page doubles as the first page you see and as the main work area.

    The old way was every time someone did something the page would refresh, the values displayed would be updated.

    To help alleviate the stress onto the sql we instead set it on a timer, so page will auto refresh every 600 seconds or when the client hits “Refresh” on the browser.

    This page basically displays the number of documents that had errors, the number that had no errors, ones that where so bad we could not display properly and ones that arrived with out any identifier.

    It also shows documents archived or rejected by users after review, and documents they exported out of the system as complete.

    I am looking for the most efficient sql query to run to get all this data. The data is in multiple tables.

    Right now my most efficient attempt is in a stored procedure and looks like this:

    Code:
    create proccedure
    @value char(50)
    AS
    
    DECLARE @AWAITINGREVIEW int
    DECLARE @REJECTED int
    DECLARE @RELEASED int
    DECLARE @ARCHIVED int
    DECLARE @NOPRVDR int
    DECLARE @TRANSERR int
    
    
    SELECT 
    @TRANSERR = (SELECT count(Col1) FROM table1 DI
    WHERE DI.col2 = 'TransErr' AND DI.col3 = @value ),
    @NOPRVDR = (SELECT count(Col1) FROM table1 DI
    WHERE  DI.col2 = 'NoPrvdr' and DI.col3 = @value )
    
    
    SELECT 
    @AWAITINGREVIEW = (SELECT count(Col1) FROM table1 DI
    WHERE  DI.Col4 = 'Pending' AND DI.col2 = 'VERIFY' AND DI.Col5 = 'Data Validation' AND DI.col3 = @value),
    @REJECTED = (SELECT count(Col1) FROM table1 DI
    WHERE  DI.Col4 = 'Pending' AND DI.col2 = 'Cancelled' AND DI.Col5 = 'Archived' AND DI.col3 = @value),
    @RELEASED = (SELECT count(Col1) FROM table1 DI
    WHERE  DI.col2 = 'Release' AND DI.Col5 = 'Export' AND DI.col3 = @value),
    @ARCHIVED = (SELECT countCol1) FROM table1 DI
    WHERE  DI.Col4 = 'Complete'  AND DI.Col5 = 'Archived' AND DI.col3 = @value)
    
    
    SELECT @TRANSERR as TRANSERR, @NOPRVDR as NOPRVDR, @AWAITINGREVIEW as AWAITINGREVIEW, @REJECTED as REJECTED, @RELEASED as RELEASED, @ARCHIVED as ARCHIVED
    
    GO
    Let me know what you think.

    Using SQL Server 2000
  • Jim Doherty
    Recognized Expert Contributor
    • Aug 2007
    • 897

    #2
    Originally posted by Javilen
    Hello,

    I am looking for some Query Optimization help,

    We are running a web application that is growing experientially in traffic and we are not running into Problems with Deadlocks on the SQL database.

    After analyzing the logs 90% of our deadlocks happen from the main login page(the page hit after login)

    This page doubles as the first page you see and as the main work area.

    The old way was every time someone did something the page would refresh, the values displayed would be updated.

    To help alleviate the stress onto the sql we instead set it on a timer, so page will auto refresh every 600 seconds or when the client hits “Refresh” on the browser.

    This page basically displays the number of documents that had errors, the number that had no errors, ones that where so bad we could not display properly and ones that arrived with out any identifier.

    It also shows documents archived or rejected by users after review, and documents they exported out of the system as complete.

    I am looking for the most efficient sql query to run to get all this data. The data is in multiple tables.

    Right now my most efficient attempt is in a stored procedure and looks like this:

    Code:
    create proccedure
    @value char(50)
    AS
     
    DECLARE @AWAITINGREVIEW int
    DECLARE @REJECTED int
    DECLARE @RELEASED int
    DECLARE @ARCHIVED int
    DECLARE @NOPRVDR int
    DECLARE @TRANSERR int
     
     
    SELECT 
    @TRANSERR = (SELECT count(Col1) FROM table1 DI
    WHERE DI.col2 = 'TransErr' AND DI.col3 = @value ),
    @NOPRVDR = (SELECT count(Col1) FROM table1 DI
    WHERE DI.col2 = 'NoPrvdr' and DI.col3 = @value )
     
     
    SELECT 
    @AWAITINGREVIEW = (SELECT count(Col1) FROM table1 DI
    WHERE DI.Col4 = 'Pending' AND DI.col2 = 'VERIFY' AND DI.Col5 = 'Data Validation' AND DI.col3 = @value),
    @REJECTED = (SELECT count(Col1) FROM table1 DI
    WHERE DI.Col4 = 'Pending' AND DI.col2 = 'Cancelled' AND DI.Col5 = 'Archived' AND DI.col3 = @value),
    @RELEASED = (SELECT count(Col1) FROM table1 DI
    WHERE DI.col2 = 'Release' AND DI.Col5 = 'Export' AND DI.col3 = @value),
    @ARCHIVED = (SELECT countCol1) FROM table1 DI
    WHERE DI.Col4 = 'Complete' AND DI.Col5 = 'Archived' AND DI.col3 = @value)
     
     
    SELECT @TRANSERR as TRANSERR, @NOPRVDR as NOPRVDR, @AWAITINGREVIEW as AWAITINGREVIEW, @REJECTED as REJECTED, @RELEASED as RELEASED, @ARCHIVED as ARCHIVED
     
    GO
    Let me know what you think.

    Using SQL Server 2000

    Seems pretty much standard to me? make sure indexes are on the correct columns in any WHERE clauses. You might also want to consider the WITH (NOLOCK) table hint in the FROM clause of a SELECT. (if you can afford your table not to be locked during a select that is..performance is improved if you can.... less overhead for the server)

    Lookup the hint in SQL (Books online) for a full description of its useage)

    Regards

    Jim :)

    Comment

    • Javilen
      New Member
      • Sep 2007
      • 20

      #3
      Hey,

      That is exactly what we needed, I implemented it and found immediately a decrease in the Deadlocking.

      I also think that just the changes this made for the main page will have a residual effect through out the rest of the site which is nice.

      We will be running it like this for a week to monitor the actual improvements but I wanted to thank you for the answer you gave as it seems to have worked wonderfully.

      Thanks

      Comment

      • Jim Doherty
        Recognized Expert Contributor
        • Aug 2007
        • 897

        #4
        Originally posted by Javilen
        Hey,

        That is exactly what we needed, I implemented it and found immediately a decrease in the Deadlocking.

        I also think that just the changes this made for the main page will have a residual effect through out the rest of the site which is nice.

        We will be running it like this for a week to monitor the actual improvements but I wanted to thank you for the answer you gave as it seems to have worked wonderfully.

        Thanks
        You're very welcome I am pleased it worked for you

        Regards

        Jim :)

        Comment

        Working...