indexing tables...still slow 1

[stetou]

hi,
My table contains 3 000 000 records. My queries are very slow. I've indexed my 6 foreign keys without selecting any option in the Manages indexes box. It doesn't improve performance at all.
I have no clue what to check next to solve this problem. Any suggestions would be useful...
Thanks!!

 

[vongrunt]

Select fewest records as possible.
Avoid LIKE '%blah%' expressions if possible.
Don't use functions on table side of expressions... if possible.
All foreign/primary key column combos should have the same data type and scale/prec/length.
Etc etc.

In other words: post query here (assuming it is not 500+ lines long and written all in caps ).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

Check your execution plan. Are you actually using the indexes?

Any filtering that is happening should also be indexed. Is it?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[stetou]

Unfortunatly I don't have the query....!!!

I'm using an OLAP-client software. It is a ROLAP cube and I didn't use Analysis services to build it. The queries are SQL, not MDX. That'S why I didn't posted my thread in the OLAP Forum.
It's the first time I use the software with that many records (which is not very big for a data warehouse). So I guess it is an index problem. MAybe I'm wrong.
thanks!

 

[druer]

Queries to populate OLAP cubes shouldn't be run during the day because the very nature of them is to expand data that is normalized and purposely expand it. They are typically brutal on the server and should be run off hours.

However, you can capture the queries that are being executed using the SQL Profiler tool. Turn it on and start capturing with it, then run your tool and the profiler will capture the actual commands that are getting executed against your server. Then you can grab the queries 1 by one out of the Profiler and execute them in the Query Analyzer to see what the real hold up is. The queries likely contain other criteria for pulling other than the FK's themselves so until you see the query you can't create the indexes. If you aren't that familiar with Query Analyzer and how to read and evaluate the Execution Plan, you can use the profiler to capture a trace and then run the Index Wizard using your trace data. It will "automagically" make indexing suggestions for you. Keep in mind that you don't want to go crazy indexes just for these things, that could negatively impact the performance of your OLTP system.

 

[mrdenny]

Run profiler and have it give you the queries. Profiler can also give you the text execution plan. For the GUI plan you'll have to take the queries and run then in Query Analyzer.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[stetou]

Great!!
I Queried the cube and got the SQL with profiler, saved the trace and ran the index wizard. With my table well indexed I got my results in 1.5 seconds instead of 9 minutes.
My boss will be proud!! haha
Thanks to all of ya!

 

[druer]

Have your boss mail the checks for us to .....