?
Solved

Database Performance - Big searches

Posted on 2005-05-10
14
Medium Priority
?
165 Views
Last Modified: 2010-03-19
Hi all

I have a few issues regarding performance with SQL server.

Basically its abit of an ongoing process, Customers send us spreadsheets with data which we put through a data cleansing process, this data cleansing process spits out a cleaned spreadsheet and inserts all of the records into a table called 'tblCustomerData', clients are then given the option to search this cleaned data online through search pages.

The search page itself contains about 6 or 7 list boxes (html) populated from their data with Distinct/Group by queries so that the data in the lists are relevant to their data

When data is pulled from this search page it is inserted into another table called "tblTempData", this table only has 3 colums which are foreign keys to the tblCustomerdata tables and has a unique identifier

the next page displays results on a page with a menu at the top which allows them to break down the records into different categories (this is just dont by submitting a number to a stored procedure and a recordset is sent back grouped by accordingly - this page used tblTempData as its source and this table joins on to tblcustomerdata, here is an ERD because this is getting messy:

tblCustomerData ----------------- tblTempData     (One to One relationship)

Ok, both of these pages take a good 30seconds to 1minute to load - which is quite a while

the reason for this TempTable is so they can remove unwanted search results

I have indexed the tblCustomerData properly, i have created the Clustered index on the CustomerID because this is what the customer data will be grouped by in the tblCustomerdata table - this is because more than one customer may have access to this facility.  And i have indexed all of the other fields that will be searched on, thing is i dont want to index more than i have to as this will slow the insertion process that happens with the data cleansing process

the search the users carry out can bring back anything from 5 records to 50,000 records

does anyone know of any techniques for speeding up querying?

If sorry for the length of this, any help is appreciated thanks
0
Comment
Question by:apresto
  • 6
  • 5
  • 3
14 Comments
 
LVL 3

Expert Comment

by:poaysee
ID: 13968256
Can you post your query? One of the slow reason may be its your query structure
0
 
LVL 23

Author Comment

by:apresto
ID: 13968304
Hi poaysee, thanks for replying

Its using dynamic SQL so its spits back different queries every time, about 10 different to Select and 10 different ones to delete

Ill generate one and post it in a sec
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 13968391
Could you post the sp or queries thanks
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 23

Author Comment

by:apresto
ID: 13968520
well here is the SP for the Search itself - this is the one that finds the data depending on the criteria they ask to search by - This isnt the one that groups the data

ready....

----------------------
CREATE PROCEDURE spNewAndUsedSearch

@JobID                  int,
@Sector            Varchar(8000),
@Town                  Varchar(1000),
@Locality            VarChar(1000),
@Make                  VarChar(500),
@Model            VarChar(500),
@PurchMonth            Varchar(100),
@PurchYear            VarChar(100),
@RegNo            Varchar(500),
@SaleManInit            Varchar(200),
@CustomerID            Int,
@SearchType            int,
@NewUsed            int

AS

DECLARE

@SQL      Varchar(6000)

DELETE FROM tblTempCustomerUpload WHERE JobID = @JobID

SET @SQL = 'INSERT INTO tblTEMPCustomerUpload'

SET @SQL = @SQL + ' SELECT ' + Cast(@JobID AS VarChar(10)) + ', CDID, DomicileID, PersonID FROM qryNewandUsedData WHERE CDID Is Not Null'

If @NewUsed = 1
      BEGIN
            SET @SQL = @SQL + ' AND NewOrUsed = ''Y'''
      END
If @NewUsed = 2
      BEGIN
            SET @SQL = @SQL + ' AND NewOrUsed = ''N'''
      END

If @Sector Is Not Null
    BEGIN
      SET @SQL = @SQL + ' AND SectorInd IN ' + @Sector
    END

If @Town Is Not Null
    BEGIN
      SET @SQL = @SQL + ' AND TownInd IN ' + @Town
    END

If @Locality Is Not Null
    BEGIN
      SET @SQL = @SQL + ' AND LocalityInd IN ' + @Locality
    END

IF @Make Is Not Null
    BEGIN
      SET @SQL = @SQL + ' AND CarMake IN ' + @Make
    END

IF @Model Is Not Null
    BEGIN
      SET @SQL = @SQL + ' AND CarModel IN ' + @Model
    END

If @PurchMonth Is Not Null
    BEGIN
      SET @SQL = @SQL + ' AND CASE WHEN NewOrUsed = ''Y'' THEN SubString(CONVERT(varchar(10),CAST(CASE WHEN DateOfSale = ''--/--/----'' THEN Null WHEN DateOfSale = ''--/--/--'' Then Null Else DateOfSale END AS datetime),3),4,2) ELSE SubString(CONVERT(varchar(10),CAST(CASE WHEN RegistrationDate = ''--/--/----'' THEN Null WHEN RegistrationDate = ''--/--/--'' Then Null Else RegistrationDate END AS datetime),3),4,2) END IN ' + @PurchMonth
    END

If @PurchYear Is Not Null
   BEGIN
      SET @SQL = @SQL + ' AND CASE WHEN NewOrUsed = ''Y'' THEN Right(CONVERT(varchar(10),CAST(CASE WHEN DateOfSale = ''--/--/----'' THEN Null WHEN DateOfSale = ''--/--/--'' Then Null Else DateOfSale END AS datetime),3),2) ELSE Right(CONVERT(varchar(10),CAST(CASE WHEN RegistrationDate = ''--/--/----'' THEN Null WHEN RegistrationDate = ''--/--/--'' Then Null Else RegistrationDate END AS datetime),3),2) END IN ' + @PurchYear
   END

If @RegNo Is Not Null
   BEGIN
         BEGIN
            SET @SQL = @SQL + ' AND ((SubString(VehicleLicenceNumber,3,2) IN ' + Cast(@RegNo AS Varchar(500)) + ' AND SubString(VehicleLicenceNumber,2,1) BETWEEN ''A'' AND ''Z'')) OR ((Left(VehicleLicenceNumber,1) IN ' + Cast(@RegNo AS Varchar(500))  + ' AND SubString(VehicleLicenceNumber,2,1) BETWEEN ''0'' AND ''9''))'
         END
   END

IF @SaleManInit Is Not Null
    BEGIN
      SET @SQL = @SQL + ' AND Left(SaleMansName,1) IN ' + @SaleManInit
    END

SET @SQL = @SQL + ' AND CustomerID = ' + Cast(@CustomerID AS VarChar(10))

exec(@SQL)
GO
0
 
LVL 3

Expert Comment

by:poaysee
ID: 13968699
Your query seems like use looping....
Hints: Looping store procedure will make the whole performance slow....
0
 
LVL 3

Expert Comment

by:poaysee
ID: 13968775
Can I have your last @SQL before the GO?
0
 
LVL 23

Author Comment

by:apresto
ID: 13968922
Its not looping....is it?

>>>Can I have your last @SQL before the GO?

...? what do you mean?
0
 
LVL 3

Expert Comment

by:poaysee
ID: 13969198
Sorry, I suddenly have another idea.
Can you try to drop the table tblTEMPCustomerUpload then start your query with

select ...
into tblTEMPCustomerUpload
where ...

This will be faster...
0
 
LVL 23

Author Comment

by:apresto
ID: 13969583
what wouyld the syntax be?

SET @SQL = 'SELECT INTO tblTEMPCustomerUpload ' + Cast(@JobID AS VarChar(10)) + ', CDID, DomicileID, PersonID FROM....'?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 13970211
the main reason your query is slow is mainly linked to applicative design:

------As a result of trying to setup too many condition combinations , you have way too many AND evaluation.  Passed 3, 4 conditions SQL Server query handling capabilities are subject to performance degradation without exhaustive indexing
------Making the code dynamic adds another layer of resource consumption and creates security glitches

I suggest to you doing the following

>>Evaluate which combination of attribute/conditions is the most likely to happen (you can easily establish that using Profiler)
>>Begin packaging premade indexed queries with most probable combination of attributes (depending on your data in qryNewandUsedData)
>>Write a new procedure where you can extract data from the view (combing already the most probable combination of conditions) instead of directly from the table.  Extract data from the table only in exceptional cases.  

Hope this helped...
0
 
LVL 3

Assisted Solution

by:poaysee
poaysee earned 200 total points
ID: 13970644
SELECT ' + Cast(@JobID AS VarChar(10)) + ', CDID, DomicileID, PersonID
into tblTEMPCustomerUpload
FROM qryNewandUsedData WHERE CDID Is Not Null'
0
 
LVL 23

Author Comment

by:apresto
ID: 13970813
Thanks for the replies guys

@poeysee

I just realised, SELECT INTO creates a new table, i cant create a new table each time as multiple customers might be using tblTEMPCustomerUpload at once, and they might leave their data in there until they are ready to export it else where so i dont think i can use SELECT INTO

@Racimo

i see what you mean about the conditions, i can imagine it must take some time compiling the SQL with the conditions.  

I tried indexing my Views but qryNewAndUsedData runs off of another query so that cant be indexed and the query it runs off is using aggregate functions so i cant index that one either, so im pretty screwed really

When you said to extract data from a view witht he most probable combinations of queries, how did you mean and how would you propose approaching this?
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 1800 total points
ID: 13970926
<<When you said to extract data from a view witht he most probable combinations of queries, how did you mean and how would you propose approaching this?>>
Establish the most probable combination of attributes or conditions (not queries) using Profiler or the data already in your table.  Using the data you can for instance realize that most of the data in the qryNewAndUsedData (say 70%) has a town and make not null .  You can then create an indexed view for all record with town and make not null.  Assuming you view is named view_qryNewAndUsedData you can select your data directly from the view the same way you would from the original table.  this way in 70 % of cases you final query should go faster...Hope this helps...
0
 
LVL 23

Author Comment

by:apresto
ID: 13984913
ok, problems solved, its lightning fast now even with a large amount of data

I changed the clustered index on the source table and also the primary view that was constructing the data that would be used in the search view was using a UNION - i changed the code so that it used a case statement instead and i think that was the main reason for the performance resolution

thanks everyone for your suggestions

Racismo, thanks for your valueble contribution, when you suggested changeing the views thats what triggered to change the union

Thanks all!

Ciao for now

Apresto
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

862 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