should give me output in Less than 10 seconds

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

should give me output in Less than 10 seconds

Author

Topic

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-08 : 20:33:45

HI Guys,
I am getting data from two tables.They have almost 80000records.This is a simple select statement.Purpose is to find those records which have phrases like company1 or image2.Its taking almost 20seconds to retreive data.Is there any better way so that it takes less than 10seconds.

Select L.Name_desc,L.List_foramt_id,LW.web_listing
FROM
di_listformats_webs LW
INNER JOIN
di_listformats L ON LW.List_format_id=L.list_format_id
Where((LW.web_listing LIKE '%company1%')OR
(LW.web_listing LIKE '%image2%'))
AND ((L.pub_id='cweb')
AND (L.issue_id='20030331')
AND (L.Pub_section_id='T1'))
Thank You!

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-08 : 20:58:50

The LIKE %stuff% is probably killing your statement.
If you can make it without the % on the front, that would make the query take advantage of any indexes on that field.

Can you post the execution plan? and what indexes exist on the tables in question?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-08 : 21:05:35

Michael
Refer to your statement.Can you post the execution plan? What does that mean?
And there are no indexes to be very honest.
As far as removing % is concerned then i am not sure because company1 and image2 can be anywhere in the long text it doesn't have to be in the beginning or at the end so that is why i had used %in the beginning also. Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-08 : 21:15:54

No indexes?? Well, I'm sure that's probably at least part of the problem.

As far as the execution plan, in Query Analyzer click "Query" and then "Show Execution Plan." When you run your query, you'll get an extra tab at the bottom that will show you the plan.
In the plan, you want "Seeks" and not "Scans."
Index Seek or Clustered Index Seek GOOD
Index Scan or Clustered Index Scan Not Good
Table Scan BAD BAD BAD!!

Give this a read as well. It will give you tips on how to make the query better.

http://www.sql-server-performance.com/query_execution_plan_analysis.asp

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-11-08 : 21:30:23

As MichaelP says, if you have LIKE %stuff% then SQL Server has no shortcuts to getting the data you want.

You could "split" the text column into individual words, and store them in a separate table, and then you could search those, which would be very fast. Or you could use the Full Text searching capability of SQL Server.

You have several other criteria in your query - so if that will narrow it down sufficiently then it may be a question of making sure that SQL Server uses that information first - so indexes on those columns could help. How many rows are involved?


SELECT COUNT(*)
FROM   di_listformats_webs LW
       JOIN di_listformats L 
           ON LW.List_format_id=L.list_format_id
WHERE
-- This bit commented out for now
--        (
--            LW.web_listing LIKE '%company1%'
--         OR LW.web_listing LIKE '%image2%'
--        )
--     AND 
           L.pub_id='cweb'
       AND L.issue_id='20030331'
       AND L.Pub_section_id='T1'

If that is a small-ish number then the query should probably be fast. If it isn't then the columns involved are not properly indexed.

Or you are using Dynamic SQL, and the Query Plan is taking longer to build than the query does to run! In which case you need a parameterised query, or a stored procedure.

Kristen

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-08 : 21:33:27

Michael,
Thanks for your reply but you know what?To be very honest i am working on a very complicated database and i am nearly new to SQL Server.I did what you told me to do and i saw that Table scan cost :71% and Table scan cost:15% is shown in execution plan tab.I really don't understand what does this mean.If you want anything else to let you know i can do that.Can't i use indexes now like on few columns which i have used in where clause.if yes then can you guide me how to do it. Thanks

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-08 : 21:35:59

Hi Kristen,
Thanks for your reply. Currently there are almost 78462 in each table and after few months it will double.Thanks

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-08 : 21:40:55

Kristen,
I will be writing a stored procedure only once my select statement doesn't take almost 20 seconds.The conditions i have put in the where close are must and i can't ignore them.If you can guide me how to do indexing i would really appreciate it. Thanks

Kristen
Test

22859 Posts

Posted - 2004-11-08 : 22:53:33

Did you run my SELECT as-is? You have 78,000 rows in the table, but how many with the partial WHERE clause in place?

I find that tuning is easier in Query Analyser using

SET STATISTICS IO ON; SET STATISTICS TIME ON

... put query here

SET STATISTICS IO OFF; SET STATISTICS TIME OFF

Set Q.A. to "Results in Text"

You need to get the Scan Count and Logical Reads as low as possible. SO run it and record the values. Create an index nd run it again and compare values.

Create two indexes as a starter:

di_listformats_webs : List_format_id
(assuming this is not the PK)

di_listformats : pub_id, issue_id, Pub_section_id

This assumes that this query is typical - i.e. the WHERE clause will usually contain criteria for pub_id, issue_id and Pub_section_id

"I will be writing a stored procedure only once my select statement doesn't take almost 20 seconds"

The trouble is that the Query Plan might be taking 19 seconds to calculate (I doubt it, but as an extreme example ...) so you won't see the benfit of a stored procedure until you actually use one! The SProc would cache the query plan - thus saving my rediculous/theroretical 19 seconds on each re-use, (after the first time).

Kristen

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-08 : 23:21:15

Kristen,
It gave me two records only with partial where clause and List _format _id is the primary key.
Table 'DI_LISTFORMATS'. Scan count 1, logical reads 3010, physical reads 0, read-ahead reads 0.
Table 'DI_LISTFORMATS_WEBS'. Scan count 1, logical reads 500, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 39594 ms, elapsed time = 44055 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-08 : 23:32:17

The Logical reads are the key thing here.
Each read causes an 8K data page to be read. So, if you add up all of the reads and times them by 8K, it tells you how much disk i/o is needed to get your results.
Your goal should be to add some indexes to get those reads as low as possible.

In this example, your server has to read about 28MB's to get your data. It has to read so much because it's scanning those tables. With the scanning, think about it like a book. To find the data you want, you have to look at every page in that book to see if it has the data you want. A SQL index is like an index in a book. It allows you to jump right to the stuff you want.

Kristen has given you good examples on what indexes to create and on how to test them. Be sure to run you query without the indexes, and look at the execution plan and the logical reads. Then add the indexes. Then re-run the query and compare the plan and reads. You want NO table scans and as few reads as possible.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-11-08 : 23:36:00

Table 'DI_LISTFORMATS'. Scan count 1, logical reads 3010, physical reads 0, read-ahead reads 0.
Table 'DI_LISTFORMATS_WEBS'. Scan count 1, logical reads 500, physical reads 0, read-ahead reads 0.

elapsed time = 44055 ms.

Everything that MichaelP said ... but that looks like one slow [or maybe very busy] machine! The query clearly needs some optimisation, but 3~4,000 logical reads shouldn't take 44 seconds ... or have I done my maths wrongly?

Kristen

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-08 : 23:41:11

HI Michael and Kristen,
Thanks for your help.Kristen you said di_listformats_webs : List_format_id
(assuming this is not the PK)This is definitely PK.Can't i index PK.Machine is not at all busy.Thanks

Kristen
Test

22859 Posts

Posted - 2004-11-08 : 23:42:04

The PK is already indexed, so no point - sorry, should have made that clear.

Kristen

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-09 : 00:15:54

Kristen may be on to something here. That took an awefulyl long timw for only 28MB's of data. That's a ton of CPU time for that thing too methinks. The Scan's could be hurting the CPU time though.

What sort of disk setup do you have in this machine? Are you sure it's not busy? Have you used perfmon to determine that?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-09 : 00:18:40

Have we tried to turn this query around and work it the other way?


Select L.Name_desc,L.List_foramt_id,LW.web_listing
FROM
di_listformats L
INNER JOIN
di_listformats_webs LW ON LW.List_format_id=L.list_format_id
Where ((L.pub_id='cweb')
AND (L.issue_id='20030331')
AND (L.Pub_section_id='T1'))
AND ((LW.web_listing LIKE '%company1%')OR
(LW.web_listing LIKE '%image2%'))

Michael
I can't believe I'm posting SQL code at 11:15 PM!

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-09 : 19:51:17

Michael,
Thanks! I can't believe you posted this code at 11:15.Wow!unbelievable
Today, i got stuck with something else.
I did try all permutation and combinations.It gave me more or less the same time.Any other suggestion.I even wrote a stored procedure also as recommended by kristen but that is also very slow.If u want i can post the coding of sp also. Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-09 : 20:12:50

Well, there's only one other thing that we could try.
break the LW.web_listing field into it's individual words using Substring and insert those words into a single colum table called words. Create a JOIN table that joins the di_listformats_webs table to the Words table so that you can write a query that looks something like this:


Select L.Name_desc,L.List_foramt_id,LW.web_listing
FROM
di_listformats L
INNER JOIN
di_listformats_webs LW ON LW.List_format_id=L.list_format_id
INNER JOIN WebWords ww ON ww.List_format_id = LW.List_format_id
INNER JOIN Words w ON w.WordID = ww.WordID
Where ((L.pub_id='cweb')
AND (L.issue_id='20030331')
AND (L.Pub_section_id='T1'))
AND ((w.Word = 'company1')OR
(w.Word = 'image2'))

It's not an easy thing to do, but what you want isn't easy.
Take a look at http://sqlteam.com/item.asp?ItemID=5857 for how to break that string down into it's individual words.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-09 : 20:23:16

Hi Michael,
Thanks for your prompt reply as usual.So are you trying to say that i should create a new table called words with a single column name words? Correct me if i am wrong. If i am correct then should datatype be varchar? Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-09 : 20:43:01

Yep, create a table with a single varchar(50) column called word.

So, once you fill it with data, this is what it should look like

this
company
name
is
the
a
for
image1
image2
etc
etc
etc

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-11-09 : 20:44:56

Michael,
These are the results first one is without indexing and second one is after indexing.Thought it might be useful for you. Thanks

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 26 ms.
Name_desc List_format_id web_listing
------------------------------------------------------------ -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ritchie Bros Manufacture [15543] 71754 <br><div id = "image2" class = "image2"> <img border="0" src="http://www.axumit.com:8080/intersoft/images/image7.jpg"></div>@@@<br><font color = "#0000FF" size="3" face="Times New Roman"><div id = "company1" class = "company1"><B>Ritchie Bros Manufacture [
Ponoka Stampeder Distributor [14266] 69949 <br><div id = "image2" class = "image2"> <img border="0" src="http://www.axumit.com:8080/intersoft/images/image6.jpg"></div>@@@<br><font color = "#0000FF" size="3" face="Times New Roman"><div id = "company1" class = "company1"><B>Ponoka Stampeder Distribut

(2 row(s) affected)

Table 'DI_LISTFORMATS'. Scan count 1, logical reads 3010, physical reads 0, read-ahead reads 0.
Table 'DI_LISTFORMATS_WEBS'. Scan count 1, logical reads 500, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 39766 ms, elapsed time = 40437 ms.

SQL Server Execution Times:
CPU time = 39766 ms, elapsed time = 40438 ms.

SQL Server Execution Times:
CPU time = 39766 ms, elapsed time = 40439 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Name_desc List_format_id web_listing
------------------------------------------------------------ -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ritchie Bros Manufacture [15543] 71754 <br><div id = "image2" class = "image2"> <img border="0" src="http://www.axumit.com:8080/intersoft/images/image7.jpg"></div>@@@<br><font color = "#0000FF" size="3" face="Times New Roman"><div id = "company1" class = "company1"><B>Ritchie Bros Manufacture [
Ponoka Stampeder Distributor [14266] 69949 <br><div id = "image2" class = "image2"> <img border="0" src="http://www.axumit.com:8080/intersoft/images/image6.jpg"></div>@@@<br><font color = "#0000FF" size="3" face="Times New Roman"><div id = "company1" class = "company1"><B>Ponoka Stampeder Distribut

(2 row(s) affected)

Table 'DI_LISTFORMATS'. Scan count 1, logical reads 3010, physical reads 0, read-ahead reads 0.
Table 'DI_LISTFORMATS_WEBS'. Scan count 1, logical reads 500, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 39016 ms, elapsed time = 39071 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources