[Israel.pm] [OT] Timeout Problem

[Georges EL OJAIMI]

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 */
SET  ANSI_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
>