Board index » delphi » MSSQL 7 - Select * VS select field names.

MSSQL 7 - Select * VS select field names.

MSSQL 7.0 with a table of 1,000,000 records.
The table is indexed on customerID

The result of the following queries is over 6500 records.

Select * from tablename
where customerID = ??

It takes over 4 minutes. It does not use the index why?

Select all the fieldnames from tablename
where customerID = ??

It takes 4 secondes and It does use the index ?

Could someone help me on understanding why the select * does not use the
index.
Is there a work arround VS writing all the field names.

If I do a select count(*) it than use the index ???

Thank you for some help.

Patrick Gentemann

 

Re:MSSQL 7 - Select * VS select field names.


Patrick,

   I don't know why, but sometimes SQL 7.0 does not always use the available
index unless it (the indexed field name) is explicitly in the SELECT
statement.  You can specify the index name in the optimizer hint:

Select *
From
   Customers (Index(myindexname))
where
   customerID = 12345

Good luck,
krf

Quote
Patrick Gentemann wrote in message <38E2535F.AD4B6...@futurtelecom.net>...
>MSSQL 7.0 with a table of 1,000,000 records.
>The table is indexed on customerID

>The result of the following queries is over 6500 records.

>Select * from tablename
>where customerID = ??

>It takes over 4 minutes. It does not use the index why?

>Select all the fieldnames from tablename
>where customerID = ??

>It takes 4 secondes and It does use the index ?

>Could someone help me on understanding why the select * does not use the
>index.
>Is there a work arround VS writing all the field names.

>If I do a select count(*) it than use the index ???

>Thank you for some help.

>Patrick Gentemann

Re:MSSQL 7 - Select * VS select field names.


Quote
Patrick Gentemann wrote in message <38E2535F.AD4B6...@futurtelecom.net>...
>MSSQL 7.0 with a table of 1,000,000 records.
>The table is indexed on customerID

>The result of the following queries is over 6500 records.

>Select * from tablename
>where customerID = ??

>It takes over 4 minutes. It does not use the index why?

>Select all the fieldnames from tablename
>where customerID = ??

>It takes 4 secondes and It does use the index ?

>Could someone help me on understanding why the select * does not use the
>index.

I don't know MSSQL so cannot give you an explanation, however, you could add
an "order by customerid", this should force it to use the index.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:MSSQL 7 - Select * VS select field names.


Thank you for the tip.

It is 2 second slower and it uses the bookmark lookup unstead of the index.
Do you know where I could find additional information.

Patrick Gentemann

Quote
Kevin Frevert wrote:
> Patrick,

>    I don't know why, but sometimes SQL 7.0 does not always use the available
> index unless it (the indexed field name) is explicitly in the SELECT
> statement.  You can specify the index name in the optimizer hint:

> Select *
> From
>    Customers (Index(myindexname))
> where
>    customerID = 12345

> Good luck,
> krf

> Patrick Gentemann wrote in message <38E2535F.AD4B6...@futurtelecom.net>...
> >MSSQL 7.0 with a table of 1,000,000 records.
> >The table is indexed on customerID

> >The result of the following queries is over 6500 records.

> >Select * from tablename
> >where customerID = ??

> >It takes over 4 minutes. It does not use the index why?

> >Select all the fieldnames from tablename
> >where customerID = ??

> >It takes 4 secondes and It does use the index ?

> >Could someone help me on understanding why the select * does not use the
> >index.
> >Is there a work arround VS writing all the field names.

> >If I do a select count(*) it than use the index ???

> >Thank you for some help.

> >Patrick Gentemann

Re:MSSQL 7 - Select * VS select field names.


ORDER BY does not forces the use of the index.
Only naming the index in the hint section allows you to use the bookmark lookup
wich is slitly slower.

Thank you for your help.

Patrick Gentemann

Quote
Wayne Niddery (TeamB) wrote:
> Patrick Gentemann wrote in message <38E2535F.AD4B6...@futurtelecom.net>...
> >MSSQL 7.0 with a table of 1,000,000 records.
> >The table is indexed on customerID

> >The result of the following queries is over 6500 records.

> >Select * from tablename
> >where customerID = ??

> >It takes over 4 minutes. It does not use the index why?

> >Select all the fieldnames from tablename
> >where customerID = ??

> >It takes 4 secondes and It does use the index ?

> >Could someone help me on understanding why the select * does not use the
> >index.

> I don't know MSSQL so cannot give you an explanation, however, you could add
> an "order by customerid", this should force it to use the index.

> --
> Wayne Niddery - WinWright Consulting
> RADBooks - http://members.home.net/wniddery/
> You have a Right to Free Speech, but not the right to make me listen, nor to
> use my property as a soapbox.

Other Threads