473,851 Members | 2,030 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query too slow! Need some performance enhancing tips!

I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.

Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.

SELECT vehicle.vehicle _id
FROM (( [vehicle]
INNER JOIN [vehicle_subj_it em_assn] on
vehicle.vehicle _id=[vehicle_subj_it em_assn].vehicle_id)
INNER JOIN [subj_item] on
[vehicle_subj_it em_assn].subj_item_id=[subj_item].subj_item_id)
INNER JOIN [template_field] on
[subj_item].subj_item_id=[template_field].subj_attr_id
WHERE
([template_field].template_field _id=@template_f ield_id) AND
([template_field].template_field _type_id=3) AND
([vehicle_subj_it em_assn].subj_item_valu e_text=@value) AND
(vehicle.end_dt m IS NOT NULL)

Thanks
Gavin
Jul 20 '05 #1
3 2791
On 1 Dec 2004 06:17:40 -0800, gi***@consultan t.com wrote:
I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements .

Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.

SELECT vehicle.vehicle _id
FROM (( [vehicle]
INNER JOIN [vehicle_subj_it em_assn] on
vehicle.vehicl e_id=[vehicle_subj_it em_assn].vehicle_id)
INNER JOIN [subj_item] on
[vehicle_subj_it em_assn].subj_item_id=[subj_item].subj_item_id)
INNER JOIN [template_field] on
[subj_item].subj_item_id=[template_field].subj_attr_id
WHERE
([template_field].template_field _id=@template_f ield_id) AND
([template_field].template_field _type_id=3) AND
([vehicle_subj_it em_assn].subj_item_valu e_text=@value) AND
(vehicle.end_d tm IS NOT NULL)

Thanks
Gavin


Hi Gavin,

I don't think you need all those paretheses and brackets. They don't hurt
performance, but they do make the query harder to read.

Also, you never use any column in subj_item. I think you can remove that
table (unless it is ppossible that some value of subj_item_id that does
not exist in subj_item does exist in vehicle_subj_it em_assn.subj_it em_id
and template_field. subj_attr_id). I don't see any way to improve on the
remaining query:

SELECT vehicle.vehicle _id
FROM vehicle
INNER JOIN vehicle_subj_it em_assn
ON vehicle.vehicle _id = vehicle_subj_it em_assn.vehicle _id
INNER JOIN template_field
ON vehicle_subj_it em_assn.subj_it em_id=template_ field.subj_attr _id
WHERE template_field. template_field_ id = @template_field _id
AND template_field. template_field_ type_id = 3
AND vehicle_subj_it em_assn.subj_it em_value_text = @value
AND vehicle.end_dtm IS NOT NULL
(untested)

An other way to optimize this is to look at your indexes. I don't think an
indexed view will do you much good (though you can always try, of course -
remember to test a typical workload, as this specific information will
return faster, but update performance will suffer) - indexed views are
often used for views with aggregations.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
(gi***@consulta nt.com) writes:
I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.

Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if
this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.


You could materialize the query into an indexed view, but I would suspect
that it is an overkill.

You can probably improve performance considerably by reviewing indexes.
96 seconds for 9 millions rows sounds like a table is being scanned
somewhere. However, I cannot do that for you, since you have not submitted
enough with information.

I would suggest that you post the CREATE TABLE and CREATE INDEX statments
for your tables, as well as the approxamite rowcount for the tables.

Also consider Hugo's note about the subj_item table. It may not fill a
function in the query. (Then again, it could serve as an EXISTS condition.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Have you checked out http://www.xprime.com

We've been using their database accelerator for MS SQL with good
results.... 5X on many of our stored procs, similar to those you
describe below.

Tony

gi***@consultan t.com wrote:
I have a stored procedure that queries a database using a Select
statement with some inner joins and conditions. With over 9 million
records it takes 1 min 36 sec to complete. This is too slow for my
requirements.

Is there any way I can optimize this query. I have thought about
using an indexed view. I haven't done one before, does anyone know if this would have potential to improve performance or indeed any other
performance enhancing techniques I might try.

SELECT vehicle.vehicle _id
FROM (( [vehicle]
INNER JOIN [vehicle_subj_it em_assn] on
vehicle.vehicle _id=[vehicle_subj_it em_assn].vehicle_id)
INNER JOIN [subj_item] on
[vehicle_subj_it em_assn].subj_item_id=[subj_item].subj_item_id)
INNER JOIN [template_field] on
[subj_item].subj_item_id=[template_field].subj_attr_id
WHERE
([template_field].template_field _id=@template_f ield_id) AND
([template_field].template_field _type_id=3) AND
([vehicle_subj_it em_assn].subj_item_valu e_text=@value) AND
(vehicle.end_dt m IS NOT NULL)

Thanks
Gavin


Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5355
by: Tim Fountain | last post by:
We've recently enabled slow query logging on a server and it's proving interesting seeing which queries are bogging things down. This one is puzzling me a little: SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status = 'normal' ORDER BY posted DESC LIMIT 5; The purpose of this query is to list the five most recent (non-deleted) comments. Here is the table structure:
1
1658
by: Thomas Bartkus | last post by:
Is it possible that the elapsed time being measured includes waiting for the client to acknowledge that it has received all the data? In *addition* to the server execution time? Documentation seems to *imply* that the slow query log only looks at server execution time. But, it doesn't acknowledge this directly and there seems to be a distinct connection between slow network pipes and slow queries. For example - even the simplest...
7
3635
by: Thomi Baechler | last post by:
Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY , dbo.HRMABZ.CONNUMB , dbo.HRM_CALENDER.Datum, dbo.HRMABZ.ABZTXT FROM dbo.HRM_CALENDER INNER JOIN dbo.HRMABZ ON dbo.HRM_CALENDER.Datum >= dbo.HRMABZ.ABZDATF
2
6240
by: AG | last post by:
I am able to run a query which runs FAst in QA but slow in the application.It takes about 16 m in QA but 1000 ms on the Application.What I wanted to know is why would the query take a long time in the application when it runs fast on SQL server? How should we try debugging it? Ajay
3
4810
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. Obviously not workable! I know where the problem is, I just don't know how to fix it. The query calls a function, and I assume it gets slow because the function runs on every record. So--is there a way to rewrite the function so it's quicker?...
4
24522
by: cefrancke | last post by:
Are there any ways to speed up a Cumulative sum in a query or perhaps another faster way to have a cumulative sum column (call a vba function?). For example, I want to sum up all values under 'Amount', and also have a field call 'Cumulative' and have the sum grow with each record. Using "MyTable", the query results are... ID or Date Amount Cumulative --------------- ------ ----------
0
1533
by: Rog | last post by:
I have two append queries that each append records from an SQL table into a temporary Access table in the frontend. All of a sudden one of them has become very slow - it takes a minute before the "you are about to run an append query" is displayed, then several more minutes to append a few hundred records. I recreated the query, but the result is still the same. Strange thing is that when I rename (or recreate) the query, so that it's...
2
2623
by: jim.clifford | last post by:
Hello. I have a slow response with a system that I am setting up. The OS is Win 2000 Server with SQL Server 2000. My first execution of the SQL procedure is slow (about 40 seconds), while the second execution is faster (approx. 3 seconds and what I expect). Using query analyser I can leave the window up and come back ( after say 10 minutes ) and my next response is will be slow (and faster straight after this). The query itself is...
1
4959
by: sunith | last post by:
I have a simple delete query like delete from table_name where (col1=:b0 and col2=:b1) This delete would actually delete around 100 records out of 4 Lakh records. This delete is slow. The columns used are the part of the primary key which is a combination of 4 cols. There is an index also on this primary key.
3
2623
by: xoinki | last post by:
hi all, I wanted to know few general details about MySQL slow query log. My slow log has a statement like # User@Host: root @ localhost here, what does this "" signify? why is this needed? I could not find any documentation expaining slow query log format. Regards,
0
9747
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,...
0
11019
Oralloy
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...
0
10670
jinu1996
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...
0
10356
tracyyun
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...
0
9506
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7906
isladogs
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...
0
7073
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();...
0
5736
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4143
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.