• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Optimization it takes too long to execute simple select statements

I am accessing two tables from a database that resides on a server different from the server I am working on.
When I run a simple select * query on these tables, Table A takes about 9 sec to return the data(9500 rows, 10 columns) the Table B takes over 2 1/2  minutes(140000 rows, 13 columns). I have to JOIN these two table, Table A has two Primary Keys and Table B has no keys defined, when I Join these two tables on the condition where the two Primary keys on Table A match the columns of same type on table B it takes over 5 minutes for the query to return the data.

Can the simple select queries be optimized for time performance?

I also ran the queries logging into the server but got the same time results!
  • 2
1 Solution
On Table B, create an index on those 2 fields that you use in the join.  This should speed it up a bit.
srafi78Author Commented:
Can you brief me on how to create an index? Can I create the index on the views for these tables.

>> Can you brief me on how to create an index? <<

CREATE INDEX <IndexName> ON <YourTable> ( <FirstColumn>, <SecondColumn>)

>> Can I create the index on the views for these tables <<

Yes you can but there a lot of restrictions on creating indexes on views.  You can look it up on Books Online and go to CREATE INDEX.

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now