Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Apollyonx83x
Starting Member
3 Posts |
 Posted - 2010-01-27 : 12:38:58
|
| [code]comm.CommandText = "SELECT DISTINCT a.MANAGER_USERID, (b.LASTNAME+ ', ' + b.FIRSTNAME + ' ' + ISNULL(b.MIDDLENAME, N'')) AS Name FROM EmployeeData AS a INNER JOIN EmployeeData AS b ON a.MANAGER_USERID = b.USERIDORDER BY NAME";[/code]Is there anything I could do to this query to improve the speed of it? Right now, its taking about 13 seconds to run the query, it returns 8019 out of 89664 rows. (Criteria returned is correct, but the delay is horrid.) I have tried a Union without the ISNULL, which presented no difference in speed.Thank you in advance for your help. To inform you what I'm doing I have a Table of Employee Data, and am filtering a Manager Listing based on Drop Down Selections, All work fine except this Query, and when I run it in the SQL Server Management Studio or on my ASP.NET page, it delays for 13 seconds. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 12:44:37
|
| what all indexes you've on table? are statistics updated? |
 |
|
|
Apollyonx83x
Starting Member
3 Posts |
Posted - 2010-01-27 : 12:56:45
|
quote:
Originally posted by visakh16
what all indexes you've on table? are statistics updated?
Indexes? None. I have read-only access. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 12:59:00
|
quote:
Originally posted by Apollyonx83x
quote:
Originally posted by visakh16
what all indexes you've on table? are statistics updated?
Indexes? None. I have read-only access.
you need to have indexes for speeding up data retrieval |
|
|
|
Apollyonx83x
Starting Member
3 Posts |
Posted - 2010-01-27 : 13:01:56
|
| Syntax-wise, it is as efficient as it can be?Also, what would I be indexing? Just the Manager_UserID? or all the columns I use? in WHERE statements, and what not? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:19:24
|
Generally all columns used in the WHERE and in any JOINs. But its a broad subject, so general advice like that is ... just "general". If it was as simple as "just do this" I'm sure MS would ahve made SQL do it for you, and you wouldn't have to do anything  .You could have indexes that "cover" the query, for example; or filtered indexes; indexes on VIEWs; indexes to partition the table ... all sorts in fact to improve performance.But if you have NO indexes at present then just some indexes on the basics will improve the performance hugely. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:21:11
|
| SQL has an Index Advisor Wizard (I haven't used it, so not sure of exact name), That can analyse your queries and advise on best-indexes to create. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:23:08
|
| "Syntax-wise, it is as efficient as it can be?"I'm never happy to see DISTINCT in the query, normally means that a JOIN is pulling in multiple rows and could be corrected so as to not do that. DISTINCT means "Get all rows, sort them, remove duplicates" which is obviously more work than "Get all rows" |
|
|
|
|
|
|
|
|
SQL Query Optimization