How to improve MS SQL query speed

navinbabu
navinbabu used Ask the Experts™
on
Hello ,

Im trying to write a webservice basically a ASP AJAX autocomplete extender to which Im passing parameters from session for the SQL and returning the results and this takes 5 to 6 secongs which is very bad .

5 to 6 seconds to populate the results int he autocomplete .

What are the things thats make the webservice slow and how can i check that ?

Im including the web service code . (
<WebMethod(EnableSession:=True)> _
    Public Function GetCompletionList(ByVal prefixText As String, ByVal count As Integer) As String()
        'MsgBox(HttpContext.Current.Session("type"))
        Dim city As String = Context.Session("city")
        Dim type As String = Context.Session("type")
        If city Is Nothing Or city = "Please Select City" Then
            'MsgBox("Please Select the City")
            Dim a As New List(Of String)
            a.Add("Please select City")
            Return a.ToArray()
        Else
            'Dim city As String = ""
            'If Context.Session("city") Is Nothing Then
            '    city = contextKey
            'Else
            '    city = Context.Session("city")
            'End If
            'MsgBox(city)

            Dim con As String = ConfigurationManager.ConnectionStrings("food").ConnectionString
            'City Id
            Dim ds1 As New SqlDataAdapter("SELECT [CityId] FROM [City] WHERE [Name] LIKE '%" & city & "%'", con)
            Dim dts1 As New DataSet
            Dim cityid As Integer
            If ds1.Fill(dts1) > 0 Then
                For Each dr1 As DataRow In dts1.Tables(0).Rows
                    cityid = dr1("CityId")
                Next
            End If
            Context.Session("cityid") = cityid
            Dim ds As New SqlDataAdapter("SELECT [ItemName],[ItemId] FROM [Items] WHERE [ItemName] LIKE '%" & prefixText & "%' AND [CityId] = '" & cityid & "' AND [ItemType] = '" & type & "'", con)
            'Dim ds As New SqlDataAdapter("SELECT [Name] FROM [City] WHERE [Name] LIKE '%" & prefixText & "%'", con)
            Dim dts As New DataSet
            Dim out As New StringBuilder
            Dim items As New List(Of String)
            If ds.Fill(dts) > 0 Then
                'out.Append("<ul>")
                For Each dr As DataRow In dts.Tables(0).Rows
                    items.Add(dr("ItemName"))
                Next
                ' Dim a As String = "Abhishek"
                'Return a.ToString
                'Dim a As String = "<a style='cursor:pointer;text-decoration:none;' onmouseover='changetext(this.innerHTML)'>" + Name + "</a><br/>"
                'out.Append("</ul>")
            Else
                'items.Add("No matching results !")
            End If
            Return items.ToArray()
        End If
    End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Always write the Query Sargable (searchable argument), this will help to use the Index.

LIKE '%" & prefixText & "%' in the Query wouldnt use the Index....

you can change that to exactly comparison with the Name than use of Like

[Name] =" & prefixText & "

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Indexes are the solution. And avoid LIKE statement but if you really need to use it at least try to don't use wildcards before the test. What I mean is to use only as: "WHERE column LIKE 'MyText%'.
In the case you posted you need to create index on City.Name and also create index on Item (ItemName, CityID, ItemType).

Good luck
Instead of like, create calculated columns as SOUNDEX.
Then search the soundex(ItemName)=Calculated_column.
Hope this helps...
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Attention:
If you use the SOUNDEX function against a table column in a WHERE clause, the Query Optimizer will ignore any available indexes and perform a table scan!!

Author

Commented:
Hey guys ,

If i avoid like how can i compare the input and populate the the autcomplete results . Also please suggest any other mods for the code I attached above  to run it fast .


Thanks
IT Engineer
Distinguished Expert 2017
Commented:
You can't. It's your choice.
I've suggested you option to use STARTS WITH (in T-SQL it's LIKE 'Text%') instead of CONTAINS (in T-SQL it's LIKE '%Text%'). This is only for SQL Server engine can use index in that column.
Even that you don't want to chance that part, I already suggested you to create indexes:
"In the case you posted you need to create index on City.Name and also create index on Item (ItemName, CityID, ItemType)."

Cheers
@VMontalvao,
soundex will be not used in the query, but you create a CALCULATED column (that is stored inside the table) and then search for the soundex of the searched word... Bye

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial