Consider this SQL Query:
-----------------------------------------------------------------
SELECT c.CASE_NBR, DATEDIFF(d, c.CREATE_DT, GETDATE()) AS Age,
c.AFFD_RCVD, c.PRV_CRD_ISS, x.RegE, x.Type, x.Fraud,
c.CUST_FN + ' ' + c.CUST_LN AS CustFullName,
c.ATM_CKCD_NBR, x.TotalLoss, x.Queue, x.Status,
c.QUEUE AS Expr1, x.CHECK_ACT_NBR , c.CUST_LN, c.SSN,
c.CREATE_DT
FROM (
SELECT TOP 9999999 cl.CASE_NBR, cl.SSN, cl.CREATE_DT,
SUM(cast(TRANS_ AMNT AS float)) AS TotalLoss,
glQueue.REFN_NM AS Queue,
glStatus.REFN_N M AS Status,
grRegE.REFN_NM AS RegE, grType.REFN_NM AS Type,
grFraud.REFN_NM AS Fraud, cl.CHECK_ACT_NB R
FROM (
((((T_CASE_LST AS cl LEFT JOIN
T_INCIDENT_LST AS il ON cl.CASE_NBR = il.CASE_NBR)
INNER JOIN T_GNRL_REFN AS glQueue
ON cl.QUEUE = glQueue.REFN_NB R)
INNER JOIN T_GNRL_REFN AS glStatus
ON cl.STATUS_CD = glStatus.REFN_N BR)
INNER JOIN T_GNRL_REFN AS grRegE
ON cl.REGE_CD = grRegE.REFN_NBR )
INNER JOIN T_GNRL_REFN AS grType
ON cl.CASE_TYPE_CD = grType.REFN_NBR
)
INNER JOIN T_GNRL_REFN AS grFraud ON cl.FRAUD_CD =
grFraud.REFN_NB R
WHERE (
((glQueue.REFN_ DESC) = 'Queue')
AND ((glStatus.REFN _DESC) = 'STATUS_CD')
AND ((grRegE.REFN_D ESC) = 'YesNo')
AND ((grType.REFN_D ESC) = 'Fraud_Code')
AND ((cl.STATUS_CD) = 0)
)
GROUP BY cl.CASE_NBR, glQueue.REFN_NM , glStatus.REFN_N M,
grRegE.REFN_NM, grType.REFN_NM, grFraud.REFN_NM ,
grFraud.REFN_DE SC, cl.CHECK_ACT_NB R,
cl.SSN, cl.CREATE_DT
HAVING (((grFraud.REFN _DESC) = 'YesNo'))
) x
LEFT OUTER JOIN T_CASE_LST c ON x.CASE_NBR = c.CASE_NBR
-----------------------------------------------------------------
1. Is there anything that can be done to speed up the query?
2. This part of the query: ... AND ((cl.STATUS_CD) = 0 ... where the 0
is actually a variable passed in via a VB application. 0 would be new
cases, and normally return around 4000 - 5000 records.
3. The SQL server, Web Server, and users, are all in different states.
4. The time to return this query where cl.STATUS_CD = 0 is about 7 -
12 seconds.
5. Is this a reasonable time for this query? What can be done to
increase the time?
6. The SQL server is indexed on T_CASE_LST.STAT US_CD and
T_INCIDENT_LST. CASE_NBR, but not on any field from T_GNRL_REFN since
T_GNRL_REFN is only a general lookup table, and contains less than 50
records.
7. I've built the query as a stored procedure, and it works, though no
measurable speed increase was obtained.
8. I have not attempted building a view to aid this, as I don't see
that helping... or will it?
9. Well: any ideas?
10. I would gladly rewrite the SQL Query if it could return the same
data faster using another method.
11. Is there a way to accomplish the joins involved with the
T_GNRL_REFN in another manner to make it quicker?
12. Is there a better way to add the values in T_INCIDENT_LST than:
.... SUM(cast(TRANS_ AMNT AS float)) AS TotalLoss ... ?
13. I don't care if its pretty, I just need it faster.
14. How can I get the summing of T_INCIDENT_LST. TRANS_AMNT without a
derived table...? I know that using the derived table is slowing it
down some.
**** Any Ideas ****
David 3 7336
> 14. How can I get the summing of T_INCIDENT_LST. TRANS_AMNT without a derived table...? I know that using the derived table is slowing it down some.
15. Simply add another join to your main query (the subselect) for the
T_CASE_LST table. Add extrema aggregates to all column names from that
table in the Selection List. This way you won't have to add columns like
ATM_CKCD_NBR and QUEUE to the GROUP BY clause. Note: you only have one
derived table in your query (the one that is aliased as x).
16. Another way to "get the summing of T_INCIDENT_LST. TRANS_AMNT"
instead of the current LEFT JOIN is by using a scalar subquery. You
would get something like:
...
SELECT ..., cl.CREATE_DT, (
SELECT SUM(cast(TRANS_ AMNT AS float))
FROM T_INCIDENT_LST AS il
WHERE cl.CASE_NBR = il.CASE_NBR
) AS TotalLoss
FROM T_CASE_LST AS cl
INNER JOIN ...
17. Are you actually trying to limit the number of rows with TOP
9999999? If not, then lose the TOP keyword, because in your query it
serves no other purpose.
18. A HAVING clause without aggregates can be moved to the WHERE clause.
This is the case here.
19. If not all columns in the list (cl.CASE_NBR, cl.SSN, cl.CREATE_DT,
glQueue.REFN_NM , glStatus.REFN_N M, grRegE.REFN_NM, grType.REFN_NM,
grFraud.REFN_NM , cl.CHECK_ACT_NB R) actively contribute to the key of the
group, then remove them from the GROUP BY clause and simply add an
extrema aggregate in the Selection List. For example, if cl.SSN is
always the same for a particular cl.CASE_NBR, then remove cl.SSN from
the GROUP BY clause, and in the Selection List change "cl.SSN" in
"MAX(cl.SSN ) AS SSN".
20. Make sure all foreign keys are indexed. Play around to see which
index should be clustered to get the best performance.
21. Add an alias to TRANS_AMNT so one can see from which table it
originates.
22. BTW: maybe all you care about is speed, but the poor slub that has
to maintain this query may have a different opinion. Make sure you
document this query thoroughly. Currently, documentation seems
non-existing. There is no explanation what the query does, nor is there
any explanation why you are using a derived table, why you use
inconsistent indentation with respect to the joins, etc.
Good luck,
Gert-Jan
> 14. I'm going to work with indexing the server now.
Well, that explains a lot... It is pretty useless to try to optimize a
query if the proper indexes are not yet in place. With so many joins in
the original query and no proper indexes you will see hash joins all
over the place. What a waste of time (mostly your time).
On the other hand, you have cleaned up the query quite nicely. It is now
easy to read, and thus easy to maintain.
AFTER you added the proper indexes, you might want to experiment with
the SUM calculation as a JOIN with a GROUP BY clause instead of the
scalar subquery. One method might be faster than the other.
Gert-Jan
David (da*********@wd src.com) writes: SELECT cl.CASE_NBR, DATEDIFF(d, cl.CREATE_DT, GETDATE()) AS Age, CASE WHEN cl.REGE_CD = 0 THEN 'No' ELSE 'Yes' END AS RegE, CASE WHEN cl.CASE_TYPE_CD = 0 THEN 'VISA Courtesy' WHEN cl.CASE_TYPE_CD = 1 THEN 'CKCD' ELSE 'PIN' END AS Type, CASE WHEN cl.FRAUD_CD = 0 THEN 'No' ELSE 'Yes' END AS Fraud, (SELECT SUM(cast(il.TRA NS_AMNT AS float)) FROM T_INCIDENT_LST AS il WHERE cl.CASE_NBR = il.CASE_NBR AND il.QUEUE < 51 )AS TotalLoss, cl.CUST_FN + ' ' + cl.CUST_LN AS CustFullName, cl.QUEUE AS Queue, cl.STATUS_CD AS Status, cl.QUEUE AS Expr1, cl.CHECK_ACT_NB R, cl.AFFD_RCVD, cl.PRV_CRD_ISS, cl.CHECK_ACT_NB R, cl.CUST_LN, cl.SSN, cl.CREATE_DT FROM T_CASE_LST cl WHERE cl.STATUS_CD = 0
Whoah! That's a whole lot nicer query than the first you posted.
Good job!
Here is a variation:
SELECT cl.CASE_NBR,
DATEDIFF(d, cl.CREATE_DT, GETDATE()) AS Age,
CASE WHEN cl.REGE_CD = 0 THEN 'No' ELSE 'Yes' END AS RegE,
CASE WHEN cl.CASE_TYPE_CD = 0 THEN 'VISA Courtesy'
WHEN cl.CASE_TYPE_CD = 1 THEN 'CKCD' ELSE 'PIN' END AS Type,
CASE WHEN cl.FRAUD_CD = 0 THEN 'No' ELSE 'Yes' END AS Fraud,
il.amt AS TotalLoss,
cl.CUST_FN + ' ' + cl.CUST_LN AS CustFullName,
cl.QUEUE AS Queue, cl.STATUS_CD AS Status, cl.QUEUE AS Expr1,
cl.CHECK_ACT_NB R, cl.AFFD_RCVD, cl.PRV_CRD_ISS,
cl.CHECK_ACT_NB R, cl.CUST_LN, cl.SSN, cl.CREATE_DT
FROM T_CASE_LST cl
JOIN (SELECT il.CASE_NBR, amt = SUM(il.TRANS_AM NT)
FROM T_INCIDENT_LST
WHERE il.QUEUE < 51) AS il ON il.CASE_NBR = cl.CASE_NBR
WHERE cl.STATUS_CD = 0
I don't know if it will actually run faster, but it is worth to
give it a shot. I also took out the CAST, but if you have a good
reason for it, put it back. It is not the CAST that kills you
anyway.
By now the query is so simple, so it is difficult find suggestions
to speed it up. But two things to check:
o You have clustered index on STATUS_CD.
o CASE_NBR have the same data type in both tables.
And, oh, if there are many columns in the incident table, add a
a non-clustered index on (CASE_NBR, QUEUE, TRANS_AMNT). Also try
(QUEUE, CASE_NBR, TRANS_AMNT). If the table is narrow (that is,
there are no other columns, these are best fit for the clustered
index.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: AaronV |
last post by:
Hello,
I'd like to get the time it took a query to execute. Is there a PHP or
MySQL function that would return the execute-time of the query into a
useable format?
Similar to the way Google displays how long a search takes.
-Aaron
|
by: Troy Hakala |
last post by:
Is it possible to limit the query time in MySQL (3.x or 4.0)? For
example, I'd like to have any query that takes more than a specified
number of seconds just quit automatically. Seems dumb, but on a web
site, nobody is going to wait minutes for a query to return so they
refresh anyway. So on a busy server, MySQL ends up with several queries
running that all take a long time to finish which compound to make it
even slower. A simple time...
|
by: Chris W |
last post by:
I have recently installed suse 9.0 linux which has MySQL 4.0.15 as part
of the distribution. After I set up my users I was able to use
mysqldump and pipe it into mysql on my windows machine running 4.0.16 to
dump a database to the linux box. However, now when I try to login to
the linux box from another machine on the network I get a query timed
out error immediately after I enter the command line to start the mysql
client.
mysql -h...
|
by: Ryan Budge |
last post by:
Hi All.
I have some rather large SQL Server 2000 databases (around 60GB).
I have set up jobs to re-index the tables and update statistics every
sunday. This worked will for a few months. Now after a day or two of
using it the connections to it keep timing out. If i start the jobs
manually, all is well for two days or so.
Surely there can be a better solution to this ?
|
by: Manuel |
last post by:
i am using sqlserver 2000, and i was wondering how do i go about
setting the query time out. is there a way to configure the query
timeout for a specific user id?
| |
by: Paul Janssen |
last post by:
Hello!
Can anyone help me out with the following situation:
(a) a single query with 550 id's in the IN-clause resulting into 800+
seconds;
(b) 550 queries with a single id in the IN-clause resulting into overall
time of <60 seconds;
The table consists of 950.000 records, and the resultset consists of 205.000
records.
|
by: yongsing |
last post by:
I have a table containing 5.1 million rows. The DDL of the table is
shown below (only relevant parts shown).
CREATE TABLE XXX.XXXX (
...
SERIAL CHAR(12) NOT NULL,
ENDDATE DATE NOT NULL,
ENDTIME TIME NOT NULL,
...
...
|
by: Baller4lifeII |
last post by:
Hey there,
I've been chasing a problem for the project that I'm working on and was able to narrow it down to the ORDER BY clause in my query.
The algorithm looks like this, I take a range of time selected by the user and I need to do some calculations on the items within that range of time. However, before I calculate, I need to query for the most recent row of a specified item before that time range. So my Query statement ends up...
|
by: qfchen |
last post by:
Hi,
I had one problem when query in VB.net. It fails at first time after SQL server restart, the fail message is "timeout expired". The same query success in second time onward, How can I set timeout in VB dataSet property? I use DataSet to communicate with database. Below is the query statement I used in FillBy() function.
cnt = Me.EM_AWARE_20kV2TableAdapter.FillByTOP_EndDT(Me.DataSet_ESD2.EM_AWARE_20kV, samples, toF)
SQL statement:...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |