SQL statment takse a long long time... 
Author Message
 SQL statment takse a long long time...

hi,

I have a table with 6 fields and 4 millions of records on
MS-SQL Server (2000) on winXP.
I defined a clustered-index which is a compound of the
first four fields.
please look at the following simple SQL statment:

select * from MYTABLE where FIELD1='BLABLA' and
FIELD2='BLUBLU' and FIELD3='BLIBLI'

the result include (the expected) 8 records.
the problem is that it takes about 4 MINUTES!!

it is much more strange than it sounds, because another
SQL statment, which is much
more complex (see below) but actually is a calculation on
a similar set as the previous one,
takes only about 1/10 second.

select * from MYTABLE T1, MYTABLE T2
WHERE
T1.FIELD1='BLABLA' AND T2.FIELD1=T1.FIELD1
AND T1.FIELD2='XXX' AND  T2.FIELD2='YYY'
AND T1.FIELD3=ZZZ AND  T2.FIELD3=T1.FIELD3
AND T1.FIELD4=T2.FIELD4

well...
I looked at the execution plan of the simlpe SQL statment.

the hint on the right one is:

the title is : "clustered index scan"
"scanning a clustered index entierly or only a range"
physical operation   clustered index scan
logical operation    clustered index scan
cost 87%

the hint on the left one is:

the title is: "select"
"retrieves rows from database. allow selection at one or
many columns from one or many tables"
physical operation   select
logical operation    select
cost 13%

I ran the Index Tuning Wizard in the Thorough mode.
as a workload I used a .sql file that contain the simple
SQL statment:
select * from MYTABLE where FIELD1='BLABLA' and
FIELD2='BLUBLU' and FIELD3='BLIBLI'

the wizard recommended to add a simple index on the first
field. i let the
wizard update as he recommended.
now the speed is slow as before but the execution plane
shows on the most left
icon that it uses the non-clustered index that the wizard
created!?

anyway, the compex query run very fast and the execution
plane shows that it uses
the compound index.

the icon it self on the slow query looks deferent than
the on the fast query.
in the SLOW query the icon has a WIDE-STRAIGHT arrow.
in the FAST query the arrow is thin and goes down-right-
down.

the ARGUMENT in the SLOW query is:
OBJECT:([MYTABLE].[SIMPLEINDEX]) WHERE (([MYTABLE].

the ARGUMENT in the FAST query is:
OBJECT:([MYTABLE].[COMPOUNDINDEX]) AS [T1]) SEEK:([T1].
[FIELD1]='BLABLA'AND [T1].[FIELD2]=....

Thanks in advance!



Sat, 07 May 2005 13:29:28 GMT
 SQL statment takse a long long time...

select * from MYTABLE where FIELD1='BLABLA' and
FIELD2='BLUBLU' and FIELD3='BLIBLI'

What does the table schema look like and what is the clustered index? You
say it has 4 columns? This where clause only has 3 columns. The index won't
be used if you're not referencing the first column in the index.

--

Brian Moran
SQL Server MVP
SQL Server Magazine Columnist


Quote:
> hi,

> I have a table with 6 fields and 4 millions of records on
> MS-SQL Server (2000) on winXP.
> I defined a clustered-index which is a compound of the
> first four fields.
> please look at the following simple SQL statment:

> select * from MYTABLE where FIELD1='BLABLA' and
> FIELD2='BLUBLU' and FIELD3='BLIBLI'

> the result include (the expected) 8 records.
> the problem is that it takes about 4 MINUTES!!

> it is much more strange than it sounds, because another
> SQL statment, which is much
> more complex (see below) but actually is a calculation on
> a similar set as the previous one,
> takes only about 1/10 second.

> select * from MYTABLE T1, MYTABLE T2
> WHERE
> T1.FIELD1='BLABLA' AND T2.FIELD1=T1.FIELD1
> AND T1.FIELD2='XXX' AND  T2.FIELD2='YYY'
> AND T1.FIELD3=ZZZ AND  T2.FIELD3=T1.FIELD3
> AND T1.FIELD4=T2.FIELD4

> well...
> I looked at the execution plan of the simlpe SQL statment.

> the hint on the right one is:

> the title is : "clustered index scan"
> "scanning a clustered index entierly or only a range"
> physical operation   clustered index scan
> logical operation    clustered index scan
> cost 87%

> the hint on the left one is:

> the title is: "select"
> "retrieves rows from database. allow selection at one or
> many columns from one or many tables"
> physical operation   select
> logical operation    select
> cost 13%

> I ran the Index Tuning Wizard in the Thorough mode.
> as a workload I used a .sql file that contain the simple
> SQL statment:
> select * from MYTABLE where FIELD1='BLABLA' and
> FIELD2='BLUBLU' and FIELD3='BLIBLI'

> the wizard recommended to add a simple index on the first
> field. i let the
> wizard update as he recommended.
> now the speed is slow as before but the execution plane
> shows on the most left
> icon that it uses the non-clustered index that the wizard
> created!?

> anyway, the compex query run very fast and the execution
> plane shows that it uses
> the compound index.

> the icon it self on the slow query looks deferent than
> the on the fast query.
> in the SLOW query the icon has a WIDE-STRAIGHT arrow.
> in the FAST query the arrow is thin and goes down-right-
> down.

> the ARGUMENT in the SLOW query is:
> OBJECT:([MYTABLE].[SIMPLEINDEX]) WHERE (([MYTABLE].

> the ARGUMENT in the FAST query is:
> OBJECT:([MYTABLE].[COMPOUNDINDEX]) AS [T1]) SEEK:([T1].
> [FIELD1]='BLABLA'AND [T1].[FIELD2]=....

> Thanks in advance!



Sat, 07 May 2005 16:42:30 GMT
 SQL statment takse a long long time...
the fields in the where clause are the first 3 fields of
the clustered-index.

"interesting" development:
i copied the table to another SQL-Server version 7 which
is run on windows 2000.
the problem does not exist there. same SQL-statment takse
les than 1/10 of a second.

so, what the [beeeep] should i tell my clients? "don't
work on SQL-Server 2000" or "don't work on winXP"

any idea for why is it happen or what can i do in order
to fix it would be greatlly appreciated.

Thanks.

Quote:
>-----Original Message-----

>select * from MYTABLE where FIELD1='BLABLA' and
>FIELD2='BLUBLU' and FIELD3='BLIBLI'

>What does the table schema look like and what is the

clustered index? You
Quote:
>say it has 4 columns? This where clause only has 3

columns. The index won't
Quote:
>be used if you're not referencing the first column in
the index.

>--

>Brian Moran
>SQL Server MVP
>SQL Server Magazine Columnist



>> hi,

>> I have a table with 6 fields and 4 millions of records
on
>> MS-SQL Server (2000) on winXP.
>> I defined a clustered-index which is a compound of the
>> first four fields.
>> please look at the following simple SQL statment:

>> select * from MYTABLE where FIELD1='BLABLA' and
>> FIELD2='BLUBLU' and FIELD3='BLIBLI'

>> the result include (the expected) 8 records.
>> the problem is that it takes about 4 MINUTES!!

>> it is much more strange than it sounds, because another
>> SQL statment, which is much
>> more complex (see below) but actually is a calculation
on
>> a similar set as the previous one,
>> takes only about 1/10 second.

>> select * from MYTABLE T1, MYTABLE T2
>> WHERE
>> T1.FIELD1='BLABLA' AND T2.FIELD1=T1.FIELD1
>> AND T1.FIELD2='XXX' AND  T2.FIELD2='YYY'
>> AND T1.FIELD3=ZZZ AND  T2.FIELD3=T1.FIELD3
>> AND T1.FIELD4=T2.FIELD4

>> well...
>> I looked at the execution plan of the simlpe SQL
statment.

>> the hint on the right one is:

>> the title is : "clustered index scan"
>> "scanning a clustered index entierly or only a range"
>> physical operation   clustered index scan
>> logical operation    clustered index scan
>> cost 87%

>> the hint on the left one is:

>> the title is: "select"
>> "retrieves rows from database. allow selection at one
or
>> many columns from one or many tables"
>> physical operation   select
>> logical operation    select
>> cost 13%

>> I ran the Index Tuning Wizard in the Thorough mode.
>> as a workload I used a .sql file that contain the
simple
>> SQL statment:
>> select * from MYTABLE where FIELD1='BLABLA' and
>> FIELD2='BLUBLU' and FIELD3='BLIBLI'

>> the wizard recommended to add a simple index on the
first
>> field. i let the
>> wizard update as he recommended.
>> now the speed is slow as before but the execution plane
>> shows on the most left
>> icon that it uses the non-clustered index that the
wizard
>> created!?

>> anyway, the compex query run very fast and the
execution
>> plane shows that it uses
>> the compound index.

>> the icon it self on the slow query looks deferent than
>> the on the fast query.
>> in the SLOW query the icon has a WIDE-STRAIGHT arrow.
>> in the FAST query the arrow is thin and goes down-
right-
>> down.

>> the ARGUMENT in the SLOW query is:
>> OBJECT:([MYTABLE].[SIMPLEINDEX]) WHERE (([MYTABLE].

>> the ARGUMENT in the FAST query is:
>> OBJECT:([MYTABLE].[COMPOUNDINDEX]) AS [T1]) SEEK:([T1].
>> [FIELD1]='BLABLA'AND [T1].[FIELD2]=....

>> Thanks in advance!

>.



Sun, 08 May 2005 10:07:35 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Help Help My DBCC CheckDB run a long long time

2. Help!!! The SQL statment Tooooo long

3. Transfer Oracle Long Data value to SQL Server Long data value

4. SQL Server- Contract-Long term-Long Island

5. [LONG] Bug / Problem with SQL Server Desktop Edition (roaming profile no longer saved)

6. Truncating a LONG column to fit a PL/SQL LONG var

7. Oracle7 LONG vs. PL/SQL LONG

8. sql plus command for storing images in long or long raw

9. medium time automatically converted to long time.

10. KB Q282416 - Opening the Database Folder in SQL Server Takes a Long Time

11. Long appending time on SQL 6.5 + VC 6


 
Powered by phpBB® Forum Software