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. 6 2201
"Ryan" <ry********@hot mail.com> wrote in message
news:78******** *************** ***@posting.goo gle.com... 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.
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
Ryan (ry********@hot mail.com) writes: 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).
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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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!
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 <ra*******@higd onconsulting.co m> wrote in message news:<3f******* **************@ news.frii.net>. .. 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!
Forgot to mention, SQL 7.0 is what I'm using :-)
Ray Higdon <ra*******@higd onconsulting.co m> wrote in message news:<3f******* **************@ news.frii.net>. .. 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 (ry********@hot mail.com) writes: 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.
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, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rich |
last post by:
I'm sure it sounds kinda nutty to display 200 columns and
500,000 rows of data. But I have been pulling data from a
Lotus Notes database into Sql Server for a while now, but
Lotus Notes is starting to crack, columns getting
corrupted. Can't handle the volume of data and number of
columns. Sql Server has no problem. But displaying the
data is the big deal. The end users want to be able to
scroll acros a page to the colum of their...
|
by: ryan |
last post by:
Hi Everyone,
I'm having trouble convincing myself that Oracle is executing a query
of mine as efficiently as possible, and I'm looking for a little
guidance.
The situation is pretty simple. I just have two tables, PARENT and
CHILD.
PARENT(
pkey int primary key,
|
by: david_0 |
last post by:
What causes the query optimizer to choose a table spool\lazy spool
action in the execution plan? The explanation of "optimize rewinds"
makes little sense because my query never comes back to that table.
I'm going to have to change the query but it would be helpful if I
knew what I should be trying to avoid.
David
|
by: mamo74 |
last post by:
Hello.
I am administering a SQL Server (Enterprise Edition on Windows 2003)
from some month and can't understand what is going on in the latest
week (when the db grow a lot).
The DB is around 250G, and has one table with 1 billion rows. It is
performing in a decent way, but can't understand why a particolar table
has strong performance problem.
|
by: laurenq uantrell |
last post by:
Is it possible to use With (NOLOCK) and With (READPAST) in the same
SELECT query and what whould be the syntax?
@param int
SELECT
myRow
FROM
dbo.myTable WITH (NOLOCK)
WHERE
| |
by: Bing Wu |
last post by:
Hi Folks,
I have a very large table containing 170 million rows of coordinats:
CREATE TABLE "DB2ADMIN"."COORDINATE" (
"FID" INTEGER NOT NULL ,
"AID" INTEGER NOT NULL ,
"X" REAL NOT NULL ,
"Y" REAL NOT NULL ,
"Z" REAL NOT NULL )
|
by: TP |
last post by:
Here is my problem.
I need to display a table about which I have no information except the
table name. Using metadata I can somehow show the column names and
record values.
But my table has 1 million rows and if I do a select * then I do get
1 million rows.
I want to be able to provide page navigation as google does, page
|
by: Bruce Pullen |
last post by:
DB2 7.2 (7.1.0.68) on AIX 5.2.
I have a query that does a table scan on one database, and an index
scan on another.
The statistics on both databases are up-to-date. Indexes are
identical. Only rowcounts for one of the tables vary (it's using the
index where the table has 1,000 rows, but not where it has 10,000
rows).
|
by: sql_server_user |
last post by:
I'm trying to copy all 440 million rows from one table in my SQL Server
2005 db to another table with a different clustering scheme. After a
few test inserts that were successful (up to a million rows in 20
seconds with no problem), I crossed my fingers and went whole-hog with
the naive "insert into dest_table (column1, column2, ...) select
column1, column2, ... from source_table" but of course it ran out of
space in *both* the db log file...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |