[Israel.pm] [OT] Timeout Problem

[guy keren]

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...

--guy

On Thu, 8 Jun 2006, Georges EL OJAIMI wrote:

> Hello,
>
> 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
> AS
>  SELECT SheetID
> ,lt1.LanguageID
> ,SheetTypeID
> ,lt1.TextString AS SheetDesc
> --,lt2.TextString AS SheetName
> ,SheetScore
> ,SheetStartDateTime
> ,SheetEndDateTime
>  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
> GO
>
> /* Create the Index */
> SETANSI_PADDING,
>  ANSI_WARNINGS,
>  CONCAT_NULL_YIELDS_NULL,
>  ARITHABORT,
>  QUOTED_IDENTIFIER,
>  ANSI_NULLS ON
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
>
> CREATE UNIQUE CLUSTERED INDEX PK_SheetsMtzed ON [dbo].SheetsMtzed( SheetID, LanguageID )
>
> This is the simplest view!
>
> Best regards,
> Georges
>
> >
> > 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
> http://perl.org.il/mailman/listinfo/perl
>

-- 
guy

"For world domination - press 1,
 or dial 0, and please hold, for the creator." -- nob o. dy