Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2008
    Posts
    11

    Unhappy Unanswered: database search time -too long

    I have a database of electronic resources which had 28000 records earlier and was working fine. Now we have added a whole bunch to make it 800K records which has increased the search time to 14-22 seconds which is not acceptable. I have all the tables indexed.

    Please help me how to solve this problem. Let me know what other information I should put up here to make my problem undestandable.
    Thanks in advance,
    Archana

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    We will need the DDL of the table(s) including indexes and the DML you are using to search the tables

  3. #3
    Join Date
    Jun 2008
    Posts
    11
    One example of the query I am using on the view is:

    select BibNumber from [vw_TypesOnlinePeriodicalsJoin]
    where TiQueryValue like '%[^a-z]federal%'
    AND Type='nongov-ebooks'
    Order By TiOrderByValue

    and view is like with ~800k records

    SELECT dbo.tb_OnlinePeriodicals.PID, dbo.tb_OnlinePeriodicals.BibNumber, dbo.tb_Titles.Title, dbo.tb_OnlinePeriodicals.Hypertext,
    dbo.tb_OnlinePeriodicals.Hyperlink, dbo.tb_OnlinePeriodicals.RestrictionText, dbo.tb_OnlinePeriodicals.DirectionText,
    dbo.tb_OnlinePeriodicals.Frequency, dbo.tb_OnlinePeriodicals.UpdatedDate, dbo.tb_OnlinePeriodicals.RestAccess * 1 AS RestAccess,
    dbo.tb_OnlinePeriodicals.PublAccess * 1 AS PublAccess, dbo.tb_OnlinePeriodicals.Multiple * 1 AS Multiple,
    dbo.tb_OnlinePeriodicals.InfoGuide * 1 AS InfoGuide, dbo.tb_Titles.TiQueryValue, dbo.tb_Titles.TiOrderByValue, dbo.tb_SubjectLists.SubjectList,
    dbo.tb_SubjectLists.SLQueryValue, dbo.tb_Titles.StandardRowNum, dbo.tb_Types.Type
    FROM dbo.tb_OnlinePeriodicals INNER JOIN
    dbo.tb_Titles ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_Titles.BibNumber INNER JOIN
    dbo.tb_SubjectLists ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_SubjectLists.BibNumber INNER JOIN
    dbo.tb_Types ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_Types.BibNumber

    --archana

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by achaudhri
    select BibNumber from [vw_TypesOnlinePeriodicalsJoin]
    where TiQueryValue like '%[^a-z]federal%'
    AND Type='nongov-ebooks'
    Order By TiOrderByValue
    This will cause a table\ clustered index\ index scan. This simply means that the execution time will go up pretty well linearly with the growth in the size of the table. Not much you can do with queries like that.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pootle is correct, you can't do much to optimize this query because of the forced table scan.

    There are ways that you can optimize the performance by "thinking outside the box" and solving your problem in other ways. One option would be to enable full-text search. Depending on how your data is used and more importantly how it is changed, there may be other (less drastic) options too.

    -PatP

  6. #6
    Join Date
    Jun 2008
    Posts
    11
    see the execution plan for this in attachment:

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you use FTS with that search expression?

    I suppose other things are:
    Chuck memory at the server
    Consider table partitioning

    How many rows in tb_Titles?

  8. #8
    Join Date
    Jun 2008
    Posts
    11
    How does full text search works? we update the data like once a week delete current data & pull new data from another system. In between 2 weeks update there is no update to data.

    I have title of lenghth 1100, so even then full text search can help.
    Thanks for all your guidance.
    --ac

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also no execution plan....
    The ideal plan to post would be the output from SET SHOWPLAN_XML ON.

  10. #10
    Join Date
    Jun 2008
    Posts
    11
    ~500K rows in tb_titles,

  11. #11
    Join Date
    Jun 2008
    Posts
    11
    <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3054.00">
    <BatchSequence>
    <Batch>
    <Statements>
    <StmtSimple StatementText="SET SHOWPLAN_XML ON&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SET ON/OFF" />
    </Statements>
    </Batch>
    </BatchSequence>
    </ShowPlanXML>

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    heh. Try:
    Code:
    SET SHOWPLAN_XML ON
    GO
    
    select BibNumber from [vw_TypesOnlinePeriodicalsJoin]
    where TiQueryValue like '%[^a-z]federal%' 
    AND Type='nongov-ebooks'
    Order By TiOrderByValue 
    
    GO
    SET SHOWPLAN_XML OFF

  13. #13
    Join Date
    Jun 2008
    Posts
    11

    is it ok now

    <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3054.00">
    <BatchSequence>
    <Batch>
    <Statements>
    <StmtSimple StatementText="&#xD;&#xA;select BibNumber from [vw_TypesOnlinePeriodicalsJoin]&#xD;&#xA;where TiQueryValue like '%[^a-z]federal%' &#xD;&#xA;AND Type='nongov-ebooks'&#xD;&#xA;Order By TiOrderByValue &#xD;&#xA;&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="42.8551" StatementEstRows="5647.96" StatementOptmLevel="FULL">
    <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
    <QueryPlan CachedPlanSize="76" CompileTime="2063" CompileCPU="2063" CompileMemory="2216">
    <RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams" EstimateRows="5647.96" EstimateIO="0" EstimateCPU="0.114626" AvgRowSize="571" EstimatedTotalSubtreeCost="42.8551" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_OnlinePeriodicals]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OutputList>
    <Parallelism>
    <OrderBy>
    <OrderByColumn Ascending="1">
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OrderByColumn>
    </OrderBy>
    <RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="5647.96" EstimateIO="0.00281532" EstimateCPU="0.0338962" AvgRowSize="571" EstimatedTotalSubtreeCost="42.7405" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_OnlinePeriodicals]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OutputList>
    <MemoryFractions Input="0.303797" Output="1" />
    <Sort Distinct="0">
    <OrderBy>
    <OrderByColumn Ascending="1">
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OrderByColumn>
    </OrderBy>
    <RelOp NodeId="2" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="5647.96" EstimateIO="0" EstimateCPU="0.992933" AvgRowSize="571" EstimatedTotalSubtreeCost="42.7038" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_OnlinePeriodicals]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OutputList>
    <MemoryFractions Input="0.44898" Output="0.696203" />
    <Hash>
    <DefinedValues />

  14. #14
    Join Date
    Jun 2008
    Posts
    11
    <HashKeysBuild>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </HashKeysBuild>
    <HashKeysProbe>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Types]" Column="BibNumber" />
    </HashKeysProbe>
    <ProbeResidual>
    <ScalarOperator ScalarString="[testOnline_Res_v6].[dbo].[tb_SubjectLists].[BibNumber]=[testOnline_Res_v6].[dbo].[tb_Types].[BibNumber]">
    <Compare CompareOp="EQ">
    <ScalarOperator>
    <Identifier>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </Identifier>
    </ScalarOperator>
    <ScalarOperator>
    <Identifier>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Types]" Column="BibNumber" />
    </Identifier>
    </ScalarOperator>
    </Compare>
    </ScalarOperator>
    </ProbeResidual>
    <RelOp NodeId="3" PhysicalOp="Bitmap" LogicalOp="Bitmap Create" EstimateRows="4046.58" EstimateIO="0" EstimateCPU="1.20209" AvgRowSize="581" EstimatedTotalSubtreeCost="38.1528" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_OnlinePeriodicals]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </OutputList>
    <Bitmap>
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Column="Bitmap1024" />
    </DefinedValue>
    </DefinedValues>
    <HashKeys>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </HashKeys>
    <RelOp NodeId="4" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="4046.58" EstimateIO="0" EstimateCPU="1.20209" AvgRowSize="581" EstimatedTotalSubtreeCost="38.1528" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_OnlinePeriodicals]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </OutputList>
    <MemoryFractions Input="0.5" Output="0.55102" />
    <Hash>
    <DefinedValues />
    <HashKeysBuild>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </HashKeysBuild>
    <HashKeysProbe>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_OnlinePeriodicals]" Column="BibNumber" />
    </HashKeysProbe>
    <ProbeResidual>
    <ScalarOperator ScalarString="[testOnline_Res_v6].[dbo].[tb_OnlinePeriodicals].[BibNumber]=[testOnline_Res_v6].[dbo].[tb_SubjectLists].[BibNumber]">
    <Compare CompareOp="EQ">
    <ScalarOperator>
    <Identifier>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_OnlinePeriodicals]" Column="BibNumber" />
    </Identifier>
    </ScalarOperator>
    <ScalarOperator>
    <Identifier>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </Identifier>
    </ScalarOperator>
    </Compare>
    </ScalarOperator>
    </ProbeResidual>
    <RelOp NodeId="5" PhysicalOp="Bitmap" LogicalOp="Bitmap Create" EstimateRows="4046.58" EstimateIO="0" EstimateCPU="1.20209" AvgRowSize="581" EstimatedTotalSubtreeCost="35.5742" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </OutputList>
    <Bitmap>
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Column="Bitmap1023" />
    </DefinedValue>
    </DefinedValues>
    <HashKeys>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </HashKeys>
    <RelOp NodeId="6" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="4046.58" EstimateIO="0" EstimateCPU="1.20209" AvgRowSize="581" EstimatedTotalSubtreeCost="35.5742" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </OutputList>
    <MemoryFractions Input="1" Output="0.5" />
    <Hash>
    <DefinedValues />

  15. #15
    Join Date
    Jun 2008
    Posts
    11
    <HashKeys>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </HashKeys>
    <RelOp NodeId="6" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="4046.58" EstimateIO="0" EstimateCPU="1.20209" AvgRowSize="581" EstimatedTotalSubtreeCost="35.5742" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </OutputList>
    <MemoryFractions Input="1" Output="0.5" />
    <Hash>
    <DefinedValues />
    <HashKeysBuild>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    </HashKeysBuild>
    <HashKeysProbe>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </HashKeysProbe>
    <ProbeResidual>
    <ScalarOperator ScalarString="[testOnline_Res_v6].[dbo].[tb_Titles].[BibNumber]=[testOnline_Res_v6].[dbo].[tb_SubjectLists].[BibNumber]">
    <Compare CompareOp="EQ">
    <ScalarOperator>
    <Identifier>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    </Identifier>
    </ScalarOperator>
    <ScalarOperator>
    <Identifier>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_SubjectLists]" Column="BibNumber" />
    </Identifier>
    </ScalarOperator>
    </Compare>
    </ScalarOperator>
    </ProbeResidual>
    <RelOp NodeId="7" PhysicalOp="Bitmap" LogicalOp="Bitmap Create" EstimateRows="4046.58" EstimateIO="0" EstimateCPU="0.0706029" AvgRowSize="571" EstimatedTotalSubtreeCost="32.8179" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OutputList>
    <Bitmap>
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Column="Bitmap1022" />
    </DefinedValue>
    </DefinedValues>
    <HashKeys>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    </HashKeys>
    <RelOp NodeId="8" PhysicalOp="Parallelism" LogicalOp="Repartition Streams" EstimateRows="4046.58" EstimateIO="0" EstimateCPU="0.0706029" AvgRowSize="571" EstimatedTotalSubtreeCost="32.8179" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OutputList>
    <Parallelism PartitioningType="Hash">
    <PartitionColumns>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    </PartitionColumns>
    <RelOp NodeId="9" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="4046.58" EstimateIO="32.5076" EstimateCPU="0.133129" AvgRowSize="921" EstimatedTotalSubtreeCost="32.6408" Parallel="1" EstimateRebinds="0" EstimateRewinds="0">
    <OutputList>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </OutputList>
    <TableScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="BibNumber" />
    </DefinedValue>
    <DefinedValue>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiOrderByValue" />
    </DefinedValue>
    </DefinedValues>
    <Object Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" />
    <Predicate>
    <ScalarOperator ScalarString="[testOnline_Res_v6].[dbo].[tb_Titles].[TiQueryValue] like '%[^a-z]federal%'">
    <Intrinsic FunctionName="like">
    <ScalarOperator>
    <Identifier>
    <ColumnReference Database="[testOnline_Res_v6]" Schema="[dbo]" Table="[tb_Titles]" Column="TiQueryValue" />
    </Identifier>
    </ScalarOperator>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •