Run Faster

Hi Guys,
I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...
Many Thanks,
Regards,
Shaffiqpost the query.let us see ur query :D|||I running it on Coldfusion program :

SELECT ID,
ORDERREFERENCE,
CUSTOMER,
TRANSACTIONTYPE,
FIRSTNAME,
LASTNAME,
COMPANY,
ADDRESS,
CITY,
STATE,
ZIP,
COUNTRY,
EMAIL,
PHONE,
SHIPFIRSTNAME,
SHIPLASTNAME,
SHIPADDRESS,
SHIPCITY,
SHIPSTATE,
SHIPZIP,
SHIPCOUNTRY,
COMMENT1,
COMMENT2
FROM GTRANSACTIONRECORD
WHERE 0 = 0



AND TRANSACTIONDATETIME BETWEEN <CFQUERYPARAM VALUE="2005-02-17 00:00:00" CFSQLTYPE="CF_SQL_DATE">
AND <CFQUERYPARAM VALUE="2005-02-18 00:00:00" CFSQLTYPE="CF_SQL_DATE">


ORDER BY ORDERREFERENCE ASC|||you do not need the "0 = 0" and I would try creating a nonclustered index on TRANSACTIONDATETIME.|||Yeah, drop the 0=0, though that is not what is causing the query to run slowly.
Do you have indexes on TRANSACTIONDATETIME and ORDERREFERENCE?
Do you absolutely need the ORDER BY clause? I'm guessing that is where the slowdown is occuring, and recordset ordering is usually a presentation issue that should be handled by the interface or reporting tool.

Have you tried running this code in Query Analyzer with the SHOW EXECUTION PLAN option set?|||I think he using '0=0' for constructing where clause dynamically in application,so he dont have to check whether its first filter case or second case.|||That would be my guess too, or it was left behind by a previous coder...

Run a stored proc thru the index tuning wizard?

Is there any way to run the index tuning wizard on a stored proc, other than
copying the content of the stored proc into sql query analyzer?
I tried highlighting a sql call to the sp, and running itw on that along
with all tables in the db, but it just said it couldn't find anything to
add. And I know that is not the right answer.
Thanks in advance!
Hi Derrick
"Derrick" wrote:

> Is there any way to run the index tuning wizard on a stored proc, other than
> copying the content of the stored proc into sql query analyzer?
> I tried highlighting a sql call to the sp, and running itw on that along
> with all tables in the db, but it just said it couldn't find anything to
> add. And I know that is not the right answer.
> Thanks in advance!
>
If your stored procedure uses temporary tables then ITW will not be able to
tune it.
John

Run a stored proc thru the index tuning wizard?

Is there any way to run the index tuning wizard on a stored proc, other than
copying the content of the stored proc into sql query analyzer?
I tried highlighting a sql call to the sp, and running itw on that along
with all tables in the db, but it just said it couldn't find anything to
add. And I know that is not the right answer.
Thanks in advance!Hi Derrick
"Derrick" wrote:

> Is there any way to run the index tuning wizard on a stored proc, other th
an
> copying the content of the stored proc into sql query analyzer?
> I tried highlighting a sql call to the sp, and running itw on that along
> with all tables in the db, but it just said it couldn't find anything to
> add. And I know that is not the right answer.
> Thanks in advance!
>
If your stored procedure uses temporary tables then ITW will not be able to
tune it.
John

Run a stored proc thru the index tuning wizard?

Is there any way to run the index tuning wizard on a stored proc, other than
copying the content of the stored proc into sql query analyzer?
I tried highlighting a sql call to the sp, and running itw on that along
with all tables in the db, but it just said it couldn't find anything to
add. And I know that is not the right answer.
Thanks in advance!Hi Derrick
"Derrick" wrote:
> Is there any way to run the index tuning wizard on a stored proc, other than
> copying the content of the stored proc into sql query analyzer?
> I tried highlighting a sql call to the sp, and running itw on that along
> with all tables in the db, but it just said it couldn't find anything to
> add. And I know that is not the right answer.
> Thanks in advance!
>
If your stored procedure uses temporary tables then ITW will not be able to
tune it.
John