locked
Managing large tables (20m records) RRS feed

  • Question

  • Hi,

    I have 2008 R2 Web edition for a web based search engine app. It has a table which contains about 20 million records. The performance of search ok upto 5 concurrent users. However, when we load the system with 50 users we are getting timeout. I am sure there could be better way to handle such large tables. Please suggest.

    Thanks,
    CD

    Thursday, June 16, 2011 10:21 AM

Answers

  • SET STATISTICS PROFILE ON
    <your statement here>
    You can out to the text, not a grid results

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 16, 2011 11:54 AM
  • Thanks 

    I see  you have a join with another table , and sql server is able to use an INDEX defined EnglishFullName

    How many rows does it return?

    Do not use SELECT * , only needed column

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 16, 2011 12:43 PM

All replies

  • Indexes, partitioning, more efficient queries, system resources...

    You could post the DDL, sample data, execution plan for more info...

    Thursday, June 16, 2011 10:36 AM
  • Only a few million records? Large? Sheesh, why are people stuck in the 80's? :)

    As adb303 said, post the DDL, we'll take a look.

    Thursday, June 16, 2011 10:44 AM
    Answerer
  • CD

    1) Can you show us an execution plan of long running query?

    2) Timeout is client error message, set it to 0 and see what happens


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 16, 2011 11:05 AM
  • Uri,

    Thanks for offering help. I am not able to share the execution plan picture in here. Can you advice?

    The query is not taking long time to execute if fired as a single statement. However, when simulated for more than 20 concurrent users, it becomes really slow. And some of them get Timeout errors. CPU is utilized at 100% for the time it takes till all user requests are responded.

    Thursday, June 16, 2011 11:50 AM
  • SET STATISTICS PROFILE ON
    <your statement here>
    You can out to the text, not a grid results

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 16, 2011 11:54 AM
  •  

    10 1 select * from Citizen C where EnglishFullName like 'Shanti%Chunilal%Shah%'; 1 1 0 NULL NULL NULL NULL 391.1933 NULL NULL NULL 1.287158 NULL NULL SELECT 0 NULL
    10 1   |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[InternalVoterID], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([C].[InternalVoterID], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH NULL 391.1933 0 0.001635188 1633 1.287158 [C].[InternalVoterID], [C].[CardNo], [C].[EnglishFullName], [C].[SurNameEnglish], [C].[FirstNameEnglish], [C].[MiddleNameEnglish], [C].[UnicodeFullName], [C].[UnicodeSurName], [C].[UnicodeFirstName], [C].[UnicodeMiddleName], [C].[Relation], [C].[Sex], [C].[Age], [C].[HouseNumber], [C].[Building], [C].[Road], [C].[SectionNumber], [C].[ConstituencyNumber], [C].[PartNumber], [C].[SerialInPart], [C].[StateID], [C].[IsActive] NULL PLAN_ROW 0 1
    10 1        |--Index Seek(OBJECT:([EYadiElectionDB].[dbo].[Citizen].[IX_Citizen] AS [C]), SEEK:([C].[EnglishFullName] >= 'ShantHþþþþþþþþþþþþþþþ' AND [C].[EnglishFullName] < 'ShantJ'),  WHERE:([EYadiElectionDB].[dbo].[Citizen].[EnglishFullName] as [C].[EnglishFullName] like 'Shanti%Chunilal%Shah%') ORDERED FORWARD) 1 5 2 Index Seek Index Seek OBJECT:([EYadiElectionDB].[dbo].[Citizen].[IX_Citizen] AS [C]), SEEK:([C].[EnglishFullName] >= 'ShantHþþþþþþþþþþþþþþþ' AND [C].[EnglishFullName] < 'ShantJ'),  WHERE:([EYadiElectionDB].[dbo].[Citizen].[EnglishFullName] as [C].[EnglishFullName] like 'Shanti%Chunilal%Shah%') ORDERED FORWARD [C].[InternalVoterID], [C].[EnglishFullName] 391.1933 0.003865741 0.0005873127 37 0.004453053 [C].[InternalVoterID], [C].[EnglishFullName] NULL PLAN_ROW 0 1
    10 10        |--Clustered Index Seek(OBJECT:([EYadiElectionDB].[dbo].[Citizen].[PK__Citizen__43C650380F975522] AS [C]), SEEK:([C].[InternalVoterID]=[EYadiElectionDB].[dbo].[Citizen].[InternalVoterID] as [C].[InternalVoterID]) LOOKUP ORDERED FORWARD) 1 7 2 Clustered Index Seek Clustered Index Seek OBJECT:([EYadiElectionDB].[dbo].[Citizen].[PK__Citizen__43C650380F975522] AS [C]), SEEK:([C].[InternalVoterID]=[EYadiElectionDB].[dbo].[Citizen].[InternalVoterID] as [C].[InternalVoterID]) LOOKUP ORDERED FORWARD [C].[CardNo], [C].[SurNameEnglish], [C].[FirstNameEnglish], [C].[MiddleNameEnglish], [C].[UnicodeFullName], [C].[UnicodeSurName], [C].[UnicodeFirstName], [C].[UnicodeMiddleName], [C].[Relation], [C].[Sex], [C].[Age], [C].[HouseNumber], [C].[Building], [C].[Road], [C].[SectionNumber], [C].[ConstituencyNumber], [C].[PartNumber], [C].[SerialInPart], [C].[StateID], [C].[IsActive] 1 0.003125 0.0001581 1605 1.280725 [C].[CardNo], [C].[SurNameEnglish], [C].[FirstNameEnglish], [C].[MiddleNameEnglish], [C].[UnicodeFullName], [C].[UnicodeSurName], [C].[UnicodeFirstName], [C].[UnicodeMiddleName], [C].[Relation], [C].[Sex], [C].[Age], [C].[HouseNumber], [C].[Building], [C].[Road], [C].[SectionNumber], [C].[ConstituencyNumber], [C].[PartNumber], [C].[SerialInPart], [C].[StateID], [C].[IsActive] NULL PLAN_ROW 0 391.1933

    Thursday, June 16, 2011 11:57 AM
  • Thanks 

    I see  you have a join with another table , and sql server is able to use an INDEX defined EnglishFullName

    How many rows does it return?

    Do not use SELECT * , only needed column

     

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, June 16, 2011 12:43 PM
  • Actually, I dont know why exec plan shows join whereas query has only one table

    It returns 10 records... Exec plan below.

    (10 row(s) affected)

    Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

    -------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------

    10 1 select C.UnicodeFullName, C.Age, C.Sex, C.Relation, C.UnicodeMiddleName, C.HouseNumber, C.EnglishFullName, C.MiddleNameEnglish, C.ConstituencyNumber, C.PartNumber, C.SerialInPart,C.SectionNumber,C.SurnameEnglish from Citizen C where EnglishFullName like ' 1 1 0 NULL NULL NULL NULL 391.1933 NULL NULL NULL 1.287158 NULL NULL SELECT 0 NULL

    10 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[InternalVoterID], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([C].[InternalVoterID], [Expr1002]) OPTIMIZED WITH UNORDERED PREFETCH NULL 391.1933 0 0.001635188 788 1.287158 [C].[EnglishFullName], [C].[SurNameEnglish], [C].[MiddleNameEnglish], [C].[UnicodeFullName], [C].[UnicodeMiddleName], [C].[Relation], [C].[Sex], [C].[Age], [C].[HouseNumber], [C].[SectionNumber], [C].[ConstituencyNumber], [C].[PartNumber], [C].[SerialInPar NULL PLAN_ROW 0 1

    10 1 |--Index Seek(OBJECT:([EYadiElectionDB].[dbo].[Citizen].[IX_Citizen] AS [C]), SEEK:([C].[EnglishFullName] >= 'ShantH' AND [C].[EnglishFullName] < 'ShantJ'), WHERE:([EYadiElectionDB].[dbo].[Citizen].[EnglishFullName] as [C].[EnglishFu 1 5 2 Index Seek Index Seek OBJECT:([EYadiElectionDB].[dbo].[Citizen].[IX_Citizen] AS [C]), SEEK:([C].[EnglishFullName] >= 'ShantH' AND [C].[EnglishFullName] < 'ShantJ'), WHERE:([EYadiElectionDB].[dbo].[Citizen].[EnglishFullName] as [C].[EnglishFullName] like 'Shanti% [C].[InternalVoterID], [C].[EnglishFullName] 391.1933 0.003865741 0.0005873127 37 0.004453053 [C].[InternalVoterID], [C].[EnglishFullName] NULL PLAN_ROW 0 1

    10 10 |--Clustered Index Seek(OBJECT:([EYadiElectionDB].[dbo].[Citizen].[PK__Citizen__43C650380F975522] AS [C]), SEEK:([C].[InternalVoterID]=[EYadiElectionDB].[dbo].[Citizen].[InternalVoterID] as [C].[InternalVoterID]) LOOKUP ORDERED FORWARD) 1 7 2 Clustered Index Seek Clustered Index Seek OBJECT:([EYadiElectionDB].[dbo].[Citizen].[PK__Citizen__43C650380F975522] AS [C]), SEEK:([C].[InternalVoterID]=[EYadiElectionDB].[dbo].[Citizen].[InternalVoterID] as [C].[InternalVoterID]) LOOKUP ORDERED FORWARD [C].[SurNameEnglish], [C].[MiddleNameEnglish], [C].[UnicodeFullName], [C].[UnicodeMiddleName], [C].[Relation], [C].[Sex], [C].[Age], [C].[HouseNumber], [C].[SectionNumber], [C].[ConstituencyNumber], [C].[PartNumber], [C].[SerialInPart] 1 0.003125 0.0001581 764 1.280725 [C].[SurNameEnglish], [C].[MiddleNameEnglish], [C].[UnicodeFullName], [C].[UnicodeMiddleName], [C].[Relation], [C].[Sex], [C].[Age], [C].[HouseNumber], [C].[SectionNumber], [C].[ConstituencyNumber], [C].[PartNumber], [C].[SerialInPart] NULL PLAN_ROW 0 391.1933

    (4 row(s) affected)

     

     

    (1 row(s) affected)

    Thursday, June 16, 2011 2:11 PM