execution plan - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

execution plan

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-21 : 13:54:27

i have query that run 10 minutes
but in the execution plan:
row count 1,813,415
cost 0.007560(%)
subtree cost:577
Estimated row count:75695

by the execution it will to run few second isn't it?

SELECT
Fact_Invoice.Date,
Fact_Invoice.ProductCd,
Fact_Invoice.CustomerNo
FROM
Fact_Invoice,Dim_Calendar,Dim_FiscalDates,Products,Customers,Regions
WHERE
Fact_Invoice.Date = Dim_Calendar.Date AND
Fact_Invoice.EventDate1 = Dim_FiscalDates.Date AND
Fact_Invoice.ProductCd = Products.ProductCd AND
Fact_Invoice.CustomerNo = Customers.CustomerNo AND
Regions.CustomerNo = Customers.CustomerNo

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-21 : 13:55:27

Post the DDL of your tables, including all indexes and constraints.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 14:21:19

well it looks like you are returning ALL of the data

Is that correct?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-21 : 14:26:05

X002548- yes. i need all data

tkizer
how i get it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-21 : 14:31:19

You expect a query that will return 75k+ records to be fast? That just isn't possible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-21 : 15:26:39

yes ,why it can not run fast?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-21 : 15:30:53

Because you are returning too much data. Areas of concern are the network in between the client and the server and also memory on the client.

What are you doing with this data and why do you need all 75k+ rows?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 15:48:37

do you need to dump it?

If so, you should use bcp

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-09-21 : 17:15:17

no, it is the query of the cube (BI-OLAP-ANALYSIS)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-22 : 04:54:31

It will not take 10 minutes to return 75k rows with only 3 columns (unless you're on an Edge mobile connection or a 14.4 modem or something). Can you run the following and post the results:

SET SHOWPLAN_TEXT ON

SELECT
Fact_Invoice.Date,
Fact_Invoice.ProductCd,
Fact_Invoice.CustomerNo
FROM
Fact_Invoice,Dim_Calendar,Dim_FiscalDates,Products,Customers,Regions
WHERE 
Fact_Invoice.Date = Dim_Calendar.Date AND
Fact_Invoice.EventDate1 = Dim_FiscalDates.Date AND
Fact_Invoice.ProductCd = Products.ProductCd AND
Fact_Invoice.CustomerNo = Customers.CustomerNo AND
Regions.CustomerNo = Customers.CustomerNo

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources