List:General Discussion« Previous MessageNext Message »
From:US Data Export Date:August 14 2009 1:33pm
Subject:RE: Erratic query performance
View as plain text  
>
>Any ideas of what could be causing the varied response time on a simple
query
>when everything on the server appears to be identical from one run to
another?
>Are there settings that can be made on the server to tweak response time
for a
>database/query like this?
>
[JS] Is it possible that there are locking conflicts? They can produce the
kind of behavior you are describing.

>Here are stats on the files involved in the query and the actual query I am
>trying to run. Note that the number of receipts with amount >= 10000 is
very
>smal compared to the total number of records.
>
>Main: 900,000 records, 500 Mb (886,361 where recordtype = "INDIVIDUAL")
>  Primary key: id (int)
>Receipt: 4,500,000 records, 700 Mb (6,817 where amount <= 10000)
>  Primary key: id (int)
>  Indexed on: mainid (int)
>Committee: 4,500 records, 600Kb (1,476 where party = "D")
>  Primary key: id (int)
>  Indexed on: Comm_id (varchar(6))
>
>create temporary table tmp type = heap
>select distinct 3 filterid, m.id, "" GroupLevel, 0 GroupCum
>from main m
>left join receipt r on m.id = r.mainid
>left join campaccommon.committee c on r.comm_id = c.Comm_id
>where
>   recordtype = "INDIVIDUAL"
>   and c.party = "D"
>   and r.amount >= 10000
>
>Returns 294 records.
>
>Thanks for any insight you can offer.
>
> - Leo Siefert



Thread
Erratic query performanceLeo Siefert13 Aug
  • Re: Erratic query performanceDan Nelson13 Aug
  • RE: Erratic query performanceGavin Towey14 Aug
  • RE: Erratic query performanceUS Data Export14 Aug