[Israel.pm] [OT] Timeout Problem
Georges EL OJAIMI
g-ojaimi at cyberia.net.lb
Thu Jun 8 01:09:22 PDT 2006
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
,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 */
SET NUMERIC_ROUNDABORT OFF
CREATE UNIQUE CLUSTERED INDEX PK_SheetsMtzed ON [dbo].SheetsMtzed( SheetID, LanguageID )
This is the simplest view!
> 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 :-)
More information about the Perl