Slow SQL Query


I’m having a trouble with my SQL Server DB. Basically, I broke up the relationships (it’s a long history). After I did that, the DB queries (for example SELECT …) takes around 10 sec (I see them in the Ignition Gateway Console), before break the relationships the queries executes fast (around 2 or less).

Even I’ve created the relationships, but I can’t repair the response time of the query. I see the ‘Query Execution Plan’ and a 'Clusted Index Scan takes 75% of the query cost.


Any suggestion for get better performance?


What’s in the table that’s being scanned? Is it data to be filtered, or ordered, or something?


Yeah, it’s filtered and ordered. It’s a little strange, because I made relations with only a few tables (not all necessary in my application) and the response time is fast again.
I’m not sure why is working fast now, but it’s working.

Thank you very much and if you could recommend me some articles to read about relationships in DB. I’ve been reading something online but maybe you could know others.

If you already know a little bit about how databases work, this MSDN article has some good information on the specifics of clustered and non-clustered indexes in SQL Server.

If you need some more basic knowledge, this looks like a good intro.

A good rule of thumb is that if you can make an index exactly match the columns of your WHERE clause, things go faster. If you’ve done a lot of inserts, rebuilding that index can speed things up.

Thank you very much @KathyApplebaum.
I will read it.