Optimized query

[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:

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]

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:

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 :)

[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

[Jim Doherty]

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 :)