[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Speed up SQL Server Query

Posted on 2012-08-25
15
Medium Priority
?
809 Views
Last Modified: 2012-08-28
I'm testing my system with 10 million entries, but with a "Select top 100" query it took more than 5 minutes to execute.
Is there any way to speed up my query or the SQL Server?
When I go to my task manager, the sql server process is using more than 1GB of memory.

Imports System.Data.SqlClient

Public Class Form1
  Friend m_db As SqlConnection
  Friend Function OpenDataTable(ByVal sQry As String) As DataTable
    Dim sTable As String = ""
    Dim sStatus As String = ""
    If Not (m_db Is Nothing) Then
      Dim commandMain As SqlCommand
      Dim readerMain As SqlDataReader
      Dim dataTable As New DataTable
      commandMain = New SqlCommand(sQry, m_db)
      commandMain.CommandTimeout = 0
      readerMain = commandMain.ExecuteReader
      dataTable.Load(readerMain)
      readerMain.Close()
      OpenDataTable = dataTable
      readerMain = Nothing
      commandMain = Nothing
    Else
      OpenDataTable = Nothing
    End If
  End Function

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim sQry As String
    sQry = "SELECT top 100 Division.Name as DivName, Locations.Name AS LocName, Case When [Assets].[Select]=0 or [Assets].[Select] is null then 0 Else 1 End as [Select], [Asset Barcode] AS [Asset Barcode], Assets.Name, Assets.Description, Assets.Category, Assets.[Division ID], [Status History].Status, [Status History].[Status Date], [Status History].[Location Barcode], Assets.[Purchase Date], Assets.[Purchase Price], Assets.[Expiry Date], Assets.[Expiry value], [Status History].[History ID], Assets.[Notes],[Custom1],[Custom2],[Custom3],[Custom4],[Custom5],Case When Photo_Thumb Is Null Then 0 Else 1 End as PhotoExists FROM Assets LEFT OUTER JOIN Division ON Assets.[Division ID] = Division.ID LEFT JOIN (Locations RIGHT JOIN [Status History] ON Locations.[Location Barcode] = [Status History].[Location Barcode]) ON Assets.[Asset Barcode] = [Status History].[Asset ID] Where ( ISNULL([Assets].[Select], 0)>=0 and  ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from "
    sQry += "[Status History] Where [Asset ID] = [Asset Barcode]))) or ( ISNULL([Assets].[Select], 0)>=0 and  ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) ORDER BY [Name] ASC;"
    MyDataGridView.DataSource = OpenDataTable(sQry)
  End Sub
End Class

Open in new window

0
Comment
Question by:koossa
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1500 total points
ID: 38333149
Are the tables appropriately indexed?

It would appear you can simplify your WHERE clause to:
    WHERE   ISNULL([Assets].[Select], 0) >= 0
            AND ISNULL([Locations].[Select], 0) >= 0
            AND ([Status History].[Status Date] = (SELECT   MAX([Status Date])
                                                   FROM     [Status History]
                                                   WHERE    [Asset ID] = [Asset Barcode]
                                                  )
                 OR [Status History].[History ID] IS NULL)

Open in new window

0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 38333752
as per "acperkins" you should use appropriate index on tables and condition having in where clause.and remove subquery (if it taking time) with join this will great helps you.
and consider order by column also while creating indexes.

Thanks..
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38334528
nishantcomp2512,
as per "acperkins"
It really is my name, but if you prefer most people here call me by my name: Anthony.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 81

Expert Comment

by:arnold
ID: 38334748
Use the SQL tools to analyze the query, it will provide you suggestions as other pointed out.
The problem you have a sub query
Separate the select to get the date, and assign the data into a variable.
0
 

Author Comment

by:koossa
ID: 38337781
I only have a primary key on my main table.

Create table [Assets] ([Asset Barcode] varchar(255) PRIMARY KEY,....."

On which fields do I need to create indexes because all of then are searchable and sort-able in my software.

How do I create indexes using T-SQL then?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38337942
For testing purposes, drop the ORDER BY.

ORDER BY with TOP forces the entire result set to be generated so SQL can do the final sort and give you the TOP nnn in that specific order.

If you drop the ORDER BY, SQL will just give you a very fast any 100 (or whatever) rows (assuming the WHERE conditions and subqueries can be satisfied quickly).
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1500 total points
ID: 38338049
How do I create indexes using T-SQL then?
Without knowing the schema for your tables it is difficult to be precise, but in addition to Scott's suggestion I would make sure the following exist:
CREATE INDEX NONCLUSTERED IX_Assets_Divisions ON Assets ([Division ID])
CREATE INDEX NONCLUSTERED IX_Assets_AssetBarcode ON Assets ([Asset Barcode])

CREATE INDEX NONCLUSTERED IX_Division_DivisionID ON Divisions(ID)

CREATE INDEX NONCLUSTERED IX_Locations_LocationBarcode ON Locations([Location Barcode])
CREATE INDEX NONCLUSTERED IX_Locations_Select ON Locations([Select])

CREATE INDEX NONCLUSTERED IX_StatusHistory_LocationBarcode ON [Status History]([Location Barcode])
CREATE INDEX NONCLUSTERED IX_StatusHistory_AssetID ON [Status History]([Asset_ID])
CREATE INDEX NONCLUSTERED IX_StatusHistory_StatusDate ON [Status History]([Status Date])
CREATE INDEX NONCLUSTERED IX_StatusHistory_HistoryID ON [Status History]([History ID])

Open in new window

And don't forget that the query should be optimized as no amount of indexes is going to help there.
0
 

Author Comment

by:koossa
ID: 38339990
Thank you very much, I'm fairly new to indexes, so I'm not sure I'm doing the right thing.

I run single queries for example
Select .... from Assets Where [Name] like '%xx%' and Desc like '%yy%' and Category='zz'

But I also run separate queries on these fields eg:
Select [Category] from Assets Where [Category] like 'L%' group by Category order by Category

From this link I see there are 2 ways to add indexes.

My question is, can I add an index for each field and one for all the fields combined and is it advisable?
Eg:

CREATE NONCLUSTERED INDEX IX_Combined ON Assets ([Name], [Description], Category)
CREATE NONCLUSTERED INDEX IX_Name ON Assets ([Name])
CREATE NONCLUSTERED INDEX IX_Description ON Assets ([Description])
CREATE NONCLUSTERED INDEX IX_Category ON Assets ([Category])
0
 

Author Comment

by:koossa
ID: 38340027
The more indexes I add the faster it gets, what will the implication be if I add too much indexes?
0
 
LVL 81

Assisted Solution

by:arnold
arnold earned 500 total points
ID: 38340796
Indexes add to overhead and have to be stored increasing the size of the database.
Too many may degrade the performance and may interfere
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38341008
I run single queries for example
Select .... from Assets Where [Name] like '%xx%' and Desc like '%yy%' and Category='zz'
Indexes on [Name[ and/or [Desc] will not help you as you are using LIKE with a leading wildcard.
0
 

Author Comment

by:koossa
ID: 38341807
You said "Indexes on [Name] and/or [Desc] will not help you as you are using LIKE with a leading wildcard."

Do I understand you correctly that no indexing will improve the performance on a "like" query?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 38341967
Do I understand you correctly that no indexing will improve the performance on a "like" query?
Indexing will not help if you are using a LIKE operator with a leading wildcard

In other words:
WHERE YourColumn LIKE 'SomeValue%'  -- May benefit from an index on YourColumn

WHERE YourColumn LIKE '%SomeValue'  -- Will never benefit from an index on YourColumn
0
 

Author Closing Comment

by:koossa
ID: 38342036
Thank you VERY Much!!!
I wish I could give you more points!!!!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38342126
>> WHERE YourColumn LIKE '%SomeValue'  -- Will never benefit from an index on YourColumn <<

That's an overstatement.

If the index on YourColumn is a covering index for the query, that index alone can be scanned, instead of having to scan the entire table.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question