koossa
asked on
Speed up SQL Server Query
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.
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 ClassSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
nishantcomp2512,
as per "acperkins"
It really is my name, but if you prefer most people here call me by my name: Anthony.
as per "acperkins"
It really is my name, but if you prefer most people here call me by my name: Anthony.
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.
The problem you have a sub query
Separate the select to get the date, and assign the data into a variable.
ASKER
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?
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?
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).
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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])
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])
ASKER
The more indexes I add the faster it gets, what will the implication be if I add too much indexes?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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?
Do I understand you correctly that no indexing will improve the performance on a "like" query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you VERY Much!!!
I wish I could give you more points!!!!
I wish I could give you more points!!!!
>> 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.
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.
and consider order by column also while creating indexes.
Thanks..