[Israel.pm] [OT] Timeout Problem
choo at actcom.co.il
Thu Jun 8 14:45:11 PDT 2006
your query is entirely in SQL-land.
if you have a good DBA there - let them analyze your needs and find a way
to make the database schema more efficient for the queries you are making.
if you're hitting a database limitation - you might want to consider to
switch to a better database (e.g. either a newer version of the same
database, or an entirely different database).
database optimization is not in the scope of this mailing list - it's
completely not related to perl...
On Thu, 8 Jun 2006, Georges EL OJAIMI wrote:
> I tried to create materialized view in order to check performance but I faced another problem.
> Suppose we have table X and we do 2 joins on it. The first is an INNER JOIN and the second is LEFT JOIN, the index fails to be created. Check the below example.
> -- If we remove the comments the index fails
> CREATE VIEW SheetsMtzed WITH SCHEMABINDING
> SELECT SheetID
> ,lt1.TextString AS SheetDesc
> --,lt2.TextString AS SheetName
> from [dbo].Sheets_Base b
> join [dbo].LanguageText lt1
> on lt1.LanguageTextID = b.SheetDesc_LanguageTextID
> --left join [dbo].LanguageText lt2
> --on lt2.LanguageTextID = b.SheetName_LanguageTextID
> --and lt2.LanguageID = lt1.LanguageID
> /* Create the Index */
> ANSI_NULLS ON
> SET NUMERIC_ROUNDABORT OFF
> CREATE UNIQUE CLUSTERED INDEX PK_SheetsMtzed ON [dbo].SheetsMtzed( SheetID, LanguageID )
> This is the simplest view!
> Best regards,
> > From: Issac Goldstand <margol at beamartyr.net>
> > Date: 2006/06/08 Thu AM 11:04:42 EAT
> > To: georges at smoothdesign.net,Perl in Israel <perl at perl.org.il>
> > Subject: Re: [Israel.pm] [OT] Timeout Problem
> > Georges EL OJAIMI wrote:
> > > 6. I am thinking that one of the problems is because the replicated tables are not indexed so the estimated execution plan shows a full table scan.
> > >
> > >
> > Hi Georges,
> > So to summarize, you're running 1500 queries against 13GB of data using
> > full table scans, and wondering why it's taking so long?
> > I would get those tables indexes before bothering to do anything else.
> > If you can't, then just don't run concurrently at your bottleneck times
> > and accept that lot's of CPUs and oodles of RAM can, and will, be
> > brought to their knees by disk I/O :-)
> > Yitzchak
> Perl mailing list
> Perl at perl.org.il
"For world domination - press 1,
or dial 0, and please hold, for the creator." -- nob o. dy
More information about the Perl