Table Scans - more rows brought back than in table

[Ryan]

I have a query which is quite complex. It's based on a set of data in
a complex view which takes the data from several tables.

In this complex query, if I allow the various parts of the query to
work on the results of the view (MISView), it can take 15 minutes to
run (eek !), however, if I create a temporary table with the data from
the view and then use that for the remainder of the query, it runs in
approx 20 seconds.

Now, I have examined the execution plan (my new favourite toy) and
there is a difference (as expected). However when looking at the part
of the query that takes up most of the time, it shows that it brings
back 109,645,866 records from a table (Credit) that contains 13,002
records. This table is one that is referenced in the view (MISView)
which contains 13,653 records and does get some of it's data from the
table which is scanned (Credit).

For the record, we don't have any tables with over 100,000 records in,
so 109 million rows is going some for us. The part of the query that
runs slow does reference another copy of itself but this is necessary
for the equation that is being run.

Now I'm OK with why it's doing the table scan, but why does it bring
back substantially more data than is in the table ? Is it some
multiple of the number of records that it's trying to work out. I
assume it tries to run a seperate plan for the view as part of it's
process.

Ideally, I'm still going to go down the route of the temporary table,
but I would like to understand more about what it does first as I
don't like leaving things unanswered.

Any help would be appreciated.

[Ronnie Chee]

Re: Table Scans - more rows brought back than in table

"Ryan" <ryanofford@hot mail.com> wrote in message
news:7802b79d.0 308150608.78b88 804@posting.goo gle.com...[color=blue]
> I have a query which is quite complex. It's based on a set of data in
> a complex view which takes the data from several tables.
>
> In this complex query, if I allow the various parts of the query to
> work on the results of the view (MISView), it can take 15 minutes to
> run (eek !), however, if I create a temporary table with the data from
> the view and then use that for the remainder of the query, it runs in
> approx 20 seconds.
>
> Now, I have examined the execution plan (my new favourite toy) and
> there is a difference (as expected). However when looking at the part
> of the query that takes up most of the time, it shows that it brings
> back 109,645,866 records from a table (Credit) that contains 13,002
> records. This table is one that is referenced in the view (MISView)
> which contains 13,653 records and does get some of it's data from the
> table which is scanned (Credit).
>
> For the record, we don't have any tables with over 100,000 records in,
> so 109 million rows is going some for us. The part of the query that
> runs slow does reference another copy of itself but this is necessary
> for the equation that is being run.
>
> Now I'm OK with why it's doing the table scan, but why does it bring
> back substantially more data than is in the table ? Is it some
> multiple of the number of records that it's trying to work out. I
> assume it tries to run a seperate plan for the view as part of it's
> process.
>
> Ideally, I'm still going to go down the route of the temporary table,
> but I would like to understand more about what it does first as I
> don't like leaving things unanswered.
>
> Any help would be appreciated.[/color]

Posted and mailed
You need to tell us what version of SQL Server, DDL for table definitions,
the view, the procedures that you are using, INSERT statements for sample
data and the expected output. Without a lot more information, it is
impossible to tell what is going on.

Wild-ass guess:
Somewhere in your process you are creating a Cartesian product from your
tables hence the large result set.
Which means you are missing some equality condition between tables or some
limiting condition isn't being coded.

HTH
Ronnie

[Erland Sommarskog]

Re: Table Scans - more rows brought back than in table

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> Now, I have examined the execution plan (my new favourite toy) and
> there is a difference (as expected). However when looking at the part
> of the query that takes up most of the time, it shows that it brings
> back 109,645,866 records from a table (Credit) that contains 13,002
> records. This table is one that is referenced in the view (MISView)
> which contains 13,653 records and does get some of it's data from the
> table which is scanned (Credit).[/color]

If you are talking about the graphical showplan, that is an
estimated row count.

Why it is so high I don't know, as you did not include any tables,
SQL, view defitinion or query plan. But it could be high, if the
plans involves several passes of the table.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ray Higdon]

Re: Table Scans - more rows brought back than in table

Are you talking about logical I/O's? If so that is the number of times
SQL hits a page, not number of records. As some of the other guys have
already stated, need more info.

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ryan]

Re: Table Scans - more rows brought back than in table

OK, a little more info. SQL below. This is the version that takes
forever. If I take the data from the view MISData into a temporary
table which I use in the later part it then speeds up. FWIW this is
only a small part of the SQL but the relevant bit. The MIS view is
complex so, if it helps to have the structure, I can post it.

Thanks

Ryan


CREATE TABLE #Params (
CurrentYear Integer NULL,
CurrentPeriod Integer NULL)

/*++++++++++++++ +++++*/

INSERT INTO
#Params

SELECT
CurrentYear = 2003,
CurrentPeriod = 6

/*++++++++++++++ +++++*/

SELECT DISTINCT
c2.client,
c2.element,
c2.ConHierLevDe s3,
c2.ConHierLevDe s1,
c2.proddesc,
c2.classdesc,
c2.freq,
c2.periodno,
sum(gross-payaway) AS CommTotal,
1 AS Cases

INTO
#Temp
-- #Temp is used later on so this is still relevant at this stage.

FROM
MisData c2, #Params qp

WHERE
c2.yearno = qp.CurrentYear AND
c2.periodno <= qp.CurrentPerio d AND
c2.gross <> 0.000 AND
(SELECT Sum(x.gross - x.payaway) FROM
MisData x
WHERE x.policy_contra ct_number = c2.policy_contr act_number AND
x.PeriodNo <= qp.CurrentPerio d
GROUP BY x.policy_contra ct_number) <> 0.0000

GROUP BY
c2.Client,
c2.Element,
c2.ConHierLevDe s3,
c2.ConHierLevDe s1,
c2.ProdDesc,
c2.ClassDesc,
c2.PeriodNo,
c2.Freq



Ray Higdon <rayhigdon@higd onconsulting.co m> wrote in message news:<3f3ff224$ 0$197$75868355@ news.frii.net>. ..[color=blue]
> Are you talking about logical I/O's? If so that is the number of times
> SQL hits a page, not number of records. As some of the other guys have
> already stated, need more info.
>
> Ray Higdon MCSE, MCDBA, CCNA
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Ryan]

Re: Table Scans - more rows brought back than in table

Forgot to mention, SQL 7.0 is what I'm using :-)

Ray Higdon <rayhigdon@higd onconsulting.co m> wrote in message news:<3f3ff224$ 0$197$75868355@ news.frii.net>. ..[color=blue]
> Are you talking about logical I/O's? If so that is the number of times
> SQL hits a page, not number of records. As some of the other guys have
> already stated, need more info.
>
> Ray Higdon MCSE, MCDBA, CCNA
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Erland Sommarskog]

Re: Table Scans - more rows brought back than in table

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> OK, a little more info. SQL below. This is the version that takes
> forever. If I take the data from the view MISData into a temporary
> table which I use in the later part it then speeds up. FWIW this is
> only a small part of the SQL but the relevant bit. The MIS view is
> complex so, if it helps to have the structure, I can post it.[/color]

I see that you query refer to the view twice. And if as you say that
the view is complex, I'm not surprised that materializing the view
into a temp table has as an enourmous payoff.

Had you been on SQL 2000, you would have had the option to materialise
the view on perstitant basis by making it an indexed view.

Since you are on SQL7, you may have found the best solution. Unless
you want to replace the reference to the view with actual query behind
and see what you can simplify.

Views are a bit dangerous. You can be lead to use a view which looks
good, but which includs references to tables you don't need at all
for your query. In fact, the system I work with does not have any
views at all.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp