huge mssql database VERY slow

[daNewfie]

I have a database with 2.4 million records and it runs incredibly slow.

here is an example of a slow query

<CFQUERY NAME="iOrder" DATASOURCE="#MYDSN#">
SELECT *
FROM orders
where internetOrdered = 'Yes'
order by start asc
</cfquery>

how can I speed it up...
ps...I know very little about databases

 

[TruthInSatire]

for starters don't use *. specify the fields you want, even if you want them all.

second, try indexing.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[daNewfie]

Thanks Truth

I will get rid of the * right now but how do I index and how do I determin which field


Craig

 

[TruthInSatire]

rudy could probably answer that part better than i could, i'd wait for him to pop his head in.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[ecobb]

As TruthInSatire said, NEVER use "Select *", it's just bad programming. You may want to look into using Stored Procedures too.

Honestly, this may be a question for the database forum. You may want to check with whatever forum matches the database you're using. If your database isn't designed for optimum performance, then there's nothing CF can do to help it.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[daNewfie]

I did use a stored proc to run things as well as switch the asterisk to the field names. And I indexed the database as well...

it rund well now..


Thanks for your help..