indexes.

Hi.
I have 2 queries that are similiar
They involve 2 tables,
1) CDR which has a primary key of cdrid and has half a million rows
2) MODULE which has a primary key of moduleid and has only 240000 rows
Neither of the two tables have indexes in it aside from the clustered index
produced by the primary key constraint
the two queries are as follows
Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdrid
Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
Query #1 takes over 40 minutes to run
Query #2 takes 8 seconds to run.
Currently, I am making an indexed view across the module and cdr tables
hoping that it will speed up the performance of query #2, but in the
meantime, I don't understand why Query #2 is so significantly different in
performance than query #1. Can anyone please explain this to me?
Thanks,
-- Jasonlooks like query #1 is almost a cross join:
a true cross join:
select cdr.cdrid from cdr, module
would return 500K*250K rows, that's a lot. the criteria cdr.cdrid <>
module.cdrid
does not filter out many riows, right?|||The queries take a long time because you are using a cross join, or cartesia
n
product of the two tables.
1. select cdr.cdrid from cdr, module
this produces a result set that is 500,000 x 240,000 = 120,000,000 rows,
which is then reduced by your WHERE clause. Instead, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid NOT IN (SELECT cdrid FROM module)
or
SELECT a.cdrid FROM cdr a
WHERE NOT EXISTS (SELECT b.cdrid FROM module b WHERE a.cdrid=b.cdrid)
Similarly, for #2, try
SELECT cdr.cdrid FROM cdr WHERE cdr.cdrid IN (SELECT cdrid FROM module)
"Jason" wrote:

> Hi.
> I have 2 queries that are similiar
> They involve 2 tables,
> 1) CDR which has a primary key of cdrid and has half a million rows
> 2) MODULE which has a primary key of moduleid and has only 240000 rows
> Neither of the two tables have indexes in it aside from the clustered inde
x
> produced by the primary key constraint
> the two queries are as follows
> Query #1) select cdr.cdrid from cdr, module where cdr.cdrid <> module.cdri
d
> Query #2) select cdr.cdrid from cdr, module where cdr.cdrid = module.cdrid
> Query #1 takes over 40 minutes to run
> Query #2 takes 8 seconds to run.
> Currently, I am making an indexed view across the module and cdr tables
> hoping that it will speed up the performance of query #2, but in the
> meantime, I don't understand why Query #2 is so significantly different i
n
> performance than query #1. Can anyone please explain this to me?
> Thanks,
> -- Jason
>
>|||Alex, you are correct.
What I am really going for is something more like this:
select cdrid from cdr where cdrid not in (select cdrid from module)
In order to put this into an indexed view, I need to get rid of the subquery
though.
In a case like this, I think I need to write it as a left outer join (i
think).
so it would be like this:
select cdr.cdrid from cdr
left outer join module on cdr.cdrid <> module.cdrid
I think that that is still incorrect though because it returns far too many
results. Can someone please point out my error?
Thanks,
-- Jason|||Since you only care about matching records shouldn't you be using an INNER
join?
select cdr.cdrid from cdr
inner join module on cdr.cdrid <> module.cdrid
John
"Jason" <jason@.acd.net> wrote in message
news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
> Alex, you are correct.
> What I am really going for is something more like this:
> select cdrid from cdr where cdrid not in (select cdrid from module)
> In order to put this into an indexed view, I need to get rid of the
> subquery though.
> In a case like this, I think I need to write it as a left outer join (i
> think).
> so it would be like this:
> select cdr.cdrid from cdr
> left outer join module on cdr.cdrid <> module.cdrid
> I think that that is still incorrect though because it returns far too
> many results. Can someone please point out my error?
> Thanks,
> -- Jason
>|||I think I figured it out...
On the contrary, I only care about not-matching records... In this case, I
matched up the CDR records to the MODULE records using a left outer join...
and then used the where clause for filtering out the ones that had actual
matches... as in the following:
select cdr.cdrid, module.cdrid from cdr
left outer join module on cdr.cdrid = module.cdrid
where module.cdrid is null
seems to work, but because it has an outer join, I cant put it into an
indexed view. *sigh*
"John Kendrick" <jkendrick@.DONTneo.SPAMrr.com> wrote in message
news:eQaorzYBGHA.3396@.tk2msftngp13.phx.gbl...
> Since you only care about matching records shouldn't you be using an INNER
> join?
> select cdr.cdrid from cdr
> inner join module on cdr.cdrid <> module.cdrid
> John
> "Jason" <jason@.acd.net> wrote in message
> news:DrCdnSaeYOOKoDXe4p2dnA@.giganews.com...
>|||On Tue, 20 Dec 2005 16:06:36 -0500, Jason wrote:

>I think I figured it out...
>On the contrary, I only care about not-matching records... In this case, I
>matched up the CDR records to the MODULE records using a left outer join...
>and then used the where clause for filtering out the ones that had actual
>matches... as in the following:
>select cdr.cdrid, module.cdrid from cdr
> left outer join module on cdr.cdrid = module.cdrid
>where module.cdrid is null
>seems to work, but because it has an outer join, I cant put it into an
>indexed view. *sigh*
Hi Jason,
Any specific reason why you want this in an indexed view?
From the preceeding post, I gather that this is only in an attempt to
increase performance. Why not try the easier options first? Such as
creating a nonclustered index on module.cdrid?
CREATE INDEX NameGoesHere ON module (cdrid)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)