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
3 1475
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 :)
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
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: StinkFinger |
last post by:
All,
Been reading other posts on other forums, i.e. Nukecops.
My original code is this:
function is_active($module) {
global $prefix, $dbi;
$result = sql_query("select active from web_modules where
title='$module'", $dbi);
list ($act) = sql_fetch_row($result, $dbi);
|
by: Glen Low |
last post by:
I have written a new implemention of the std::valarray library that is
optimized to use Altivec (Apple's "Velocity Engine", part of the
PowerPC G4's in most Macintoshes and the announced IBM PPC 970). The
implementation is mostly standard conforming and is complete.
As soon as I get my shingle up on the web (1 or 2 day's time), I'll
post the library and its accompanying docs, which I call "MacSTL".
Would like comments, tests, discussions...
|
by: Peder Bacher |
last post by:
Hello :-)
My question is: If I query a partitioned view, but don't know the values
in the "where x in(<expression>)" clause, i.e.: select * from viewA
where intVal in(select intVal from tbl1) . Compared to: select * from
viewA where intVal in(5,6).
Of course "intVal" is partitioning column.
Will this result in an optimized query that searches only the relevant
tables?
|
by: Gaurav |
last post by:
http://www.sys-con.com/story/print.cfm?storyid=45250
Any comments?
Thanks
Gaurav
|
by: Mike |
last post by:
Is it still true that the managed C++ compiler will produce much better
opimizations than the C# compiler, or have some of the more
global/aggressive opimizations been rolled into the 2005 compiler?
Are simple common sub-expressions and loop invariants optimized out in the
current optimizer?
thanks,
m
| |
by: Michel Esber |
last post by:
Hello,
DB2 LUW V8 FixPack 13.
create table Table (ID varchar(20), USED char)
I need to find out the total row count per ID, as well as the row count
where USED=Y.
I could do this with a simple:
|
by: Grizlyk |
last post by:
Hello.
What can be optimised in C++ code and how i can garantee stable
behaviour below
1.
Are expression "auto volatile" can deny removing as "unused temporary"
like this:
auto volatile const class_name tmp;
|
by: bonk |
last post by:
I have a c# project as part of a larger VS 2005 solution that always
gets build optimized and I therefore can not evaluate any values while
debugging through the code ("Cannot evaluate expression because the
code of the current method is optimized."). This happens alltough the
checkbock "optimize code" in the project settings is switched off. My
question here is what are possible conditions that would make an
assembly be build as optimized...
|
by: Doranj00 |
last post by:
Hello Everyone,
Its been awhile since developing SQL code and I have a dilemma.
Now, I can think of several ways to get what I need, BUT the important thing is that I need the optimized way to do this. The application that I will put this in is legacy and has indexing, performance issues. These issues cannot be fixed until we upgrade (which will be one year).
Ok, with that said, I have the following tables (OVERLY simplified here)
...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |