May 1, 2002 at 11:56 am
Hello,
I have a serious problem with following code. It was fine until the number of records started to increase (30k) and time to process it took 5-6 minutes which will fail running on a website. The retailmemberid is not a primary key. The rm_vert_subvert table is a lookup table containing retailmemberid, verticalid, and subverticalid columns for which the verticalid and subverticalid information is found in the sub_vertical table which contains the verticalid, subverticalid, name, and sortorder column. Please advise on how the tables or query should be constructed?
Thanks in advance!
Dan Thurman
+++++++++++++++ CODE ++++++++++++++
select distinct sv.name, sv.subverticalid, sortorder, rmvs.retailmemberid
from rm_vert_subvert rmvs, sub_vertical sv
where rmvs.subverticalid = sv.subverticalid and
rmvs.verticalid = '23' and
sv.verticalid = '23' and
sv.activeyn = 'Y'
May 1, 2002 at 12:25 pm
The first thing I would check is to see if the tables have indexes where needed. If not this could be the cause of the slowness you are seeing.
Without more details, I would say an index on subverticalid for both tables could be helpful as well as an index for the column sv.verticalid and possibly another index for the column sv.activeyn.
If you want to know or see if an index is being used or not you can place your code in Query Analyzer and before running the code click on Query and select Show Execution Plan. After executing the query you will see an additional tab at the bottom of Query Analyzer. Click on it and look at the far left and see if the icons are table scans or index seeks/scans. Its best if there are no table scans.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 1, 2002 at 1:55 pm
Robert,
Thanks for confirming my suspictions, that
indexing is the answer! I ran the MS Index Wizard, and clustered indexed the columns on the rm_vert_subvert tables, and viola! Runs like a charm and -very- fast! Awesome!
Again, thanks for the advice!
Dan Thurman
quote:
The first thing I would check is to see if the tables have indexes where needed. If not this could be the cause of the slowness you are seeing.Without more details, I would say an index on subverticalid for both tables could be helpful as well as an index for the column sv.verticalid and possibly another index for the column sv.activeyn.
If you want to know or see if an index is being used or not you can place your code in Query Analyzer and before running the code click on Query and select Show Execution Plan. After executing the query you will see an additional tab at the bottom of Query Analyzer. Click on it and look at the far left and see if the icons are table scans or index seeks/scans. Its best if there are no table scans.
Robert Marda
May 1, 2002 at 1:57 pm
You're welcome.
Many performance issues can be solved with indexes when you know the query is not the problem.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply