June 3, 2009 at 7:53 am
Hello,
with the first script I create and fill two tables and with second I get data that I want. But the second query runs always about 10 seconds on my machine, regardless what indexes I set on these two tables (see comment in first script)
Am I doing something wrong or am I missunderstanding some fundamentals about indesex?
Thanks
First script:
if object_id('_temp1') is not null
drop table _temp1
if object_id('_temp2') is not null
drop table _temp2
go
create table _temp1 (id char(3))
create table _temp2 (id char(3), descr varchar(10))
--create clustered index _i_temp1_1 on _temp1 (id)
--create clustered index _i_temp2_1 on _temp2 (id)
go
with x (i, id) as (
select 1, left(newid(), 3) id
union all
select i + 1, left(newid(), 3) id
from x
where i < 32700
)
insert into _temp1 (id)
select id
from x
option (maxrecursion 32700)
declare @count int, @max-2 int
select @count = 0, @max-2 = 20
set @count = 0
while (@count < @max-2)
begin
with y (i, id, descr) as (
select 1, left(newid(), 3) id, left(newid(), 10) descr
union all
select i + 1, left(newid(), 3) id, left(newid(), 10) descr
from y
where i < 32700
)
insert into _temp2 (id, descr)
select id, descr
from y
option (maxrecursion 32700)
set @count = @count + 1
end
[/code]
Query:
[code]
select t1.id, t2.id, t2.descr
from _temp1 t1 inner join _temp2 t2 on (t1.id = t2.id)
where t1.id like '6%'
[/code]
June 3, 2009 at 8:27 am
Hi Simon,
I've used your query to show you the difference between the indexed tables, and non indexed tables.
Practically, you don't see much difference because the data you query is long, and what is long is getting the data to show in SSMS.
Here's the code I used :
set NOCount ON
if object_id('_temp1') is not null
drop table _temp1
if object_id('_temp2') is not null
drop table _temp2
go
create table _temp1 (id char(3))
create table _temp2 (id char(3), descr varchar(10))
go
with x (i, id) as (
select 1, left(newid(), 3) id
union all
select i + 1, left(newid(), 3) id
from x
where i < 32700
)
insert into _temp1 (id)
select id
from x
option (maxrecursion 32700)
declare @count int, @max-2 int
select @count = 0, @max-2 = 20
set @count = 0
while (@count < @max-2)
begin
with y (i, id, descr) as (
select 1, left(newid(), 3) id, left(newid(), 10) descr
union all
select i + 1, left(newid(), 3) id, left(newid(), 10) descr
from y
where i < 32700
)
insert into _temp2 (id, descr)
select id, descr
from y
option (maxrecursion 32700)
set @count = @count + 1
end
--Clear the cached memory
DBCC freeProcCache WITH NO_INFOMSGS;
DBCC DropCleanBuffers WITH NO_INFOMSGS;
Print '-- =============Not Specific Query Without Indexes'
SET statistics time ON
SELECT t1.id,
t2.id,
t2.descr
FROM _temp1 t1
INNER JOIN _temp2 t2
ON (t1.id = t2.id)
WHERE t1.id LIKE '6%'
SET Statistics time OFF
--Clear the cached memory
DBCC freeProcCache WITH NO_INFOMSGS;
DBCC DropCleanBuffers WITH NO_INFOMSGS;
Print ''
Print ''
Print ''
Print '-- =============More Specific Query Without Indexes'
SET statistics time ON
SELECT t1.id,
t2.id,
t2.descr
FROM _temp1 t1
INNER JOIN _temp2 t2
ON (t1.id = t2.id)
WHERE t1.id LIKE '612%'
SET Statistics time OFF
create clustered index _i_temp1_1 on _temp1 (id)
create clustered index _i_temp2_1 on _temp2 (id)
--Reindex the tables To be sure they are accurate.
DBCC DBREINDEX ('_temp1','_i_temp1_1') WITH NO_INFOMSGS
DBCC DBREINDEX ('_temp2','_i_temp2_1') WITH NO_INFOMSGS
--Clear the cached memory
DBCC freeProcCache WITH NO_INFOMSGS;
DBCC DropCleanBuffers WITH NO_INFOMSGS;
Print ''
Print ''
Print ''
Print '-- =============Not Specific Query With Cluster Index'
SET statistics time ON
SELECT t1.id,
t2.id,
t2.descr
FROM _temp1 t1
INNER JOIN _temp2 t2
ON (t1.id = t2.id)
WHERE t1.id LIKE '6%'
SET Statistics time OFF
--Clear the cached memory
DBCC freeProcCache WITH NO_INFOMSGS;
DBCC DropCleanBuffers WITH NO_INFOMSGS;
Print ''
Print ''
Print ''
Print '-- =============More Specific Query With Cluster Index'
SET statistics time ON
SELECT t1.id,
t2.id,
t2.descr
FROM _temp1 t1
INNER JOIN _temp2 t2
ON (t1.id = t2.id)
WHERE t1.id LIKE '612%'
SET Statistics time OFF
And here are my results on my dev environment:
-- =============Not Specific Query Without Indexes
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 6394 ms.
-- =============More Specific Query Without Indexes
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 312 ms.
-- =============Not Specific Query With Cluster Index
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 4918 ms.
-- =============More Specific Query With Cluster Index
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 96 ms.
As you can see, the indexes DO help you a bit on a long query, but you can see it mostly in the CPU time. The CPU time is the time it takes to actually run your query, if you have a lot of data to retrieve, it takes time, and the time elapsed show as "Elapsed time". That is the completion of the query.
So, for first example, when you used like '6%', it got a lot of data, but still, the CPU time was 297 ms without indexes and 219 ms with the cluster, which is an improvement, but not that great. You can see the total time went from over 6 seconds to about 5, which is a bit better... but still, there is a lot of data involved.
I built a second query, that is more precise, and that gets less data, and there, you can see a huge difference:
156 ms for CPU time without indexes, compared to 16 ms with indexes.. ouch, that's 10 times better..
312 ms to complete the query, vs 96 ms to complete the query, which means it took 3 times less to get your complete query.
That is, the CPU uses less time, which means your server is has more time to do other queries, and your query runs faster...
You will see a lot more improvements when working with bigger queries, bigger datasets, and more restrictive queries... But still, you GOT an improvement!
Hope that helped Simon,
Cheers,
J-F
June 4, 2009 at 11:28 am
Thanks, that explains a lot. It looks like I have to learn execution plans prior to indexes... now I am studying Execution plans on SQL in the Wild, nice and simple article 🙂
It's probably much easier to see index improvement in execution plan than just in execution time...
June 4, 2009 at 11:54 am
I'm glad it helped Simon,
Have a nice day, and yes, execution plans are REALLY useful to know, 😉
Cheers,
J-F
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply