simple select view to retrive 40 lkh records taking 2 min

Question

simple select view to retrive 40 lkh records taking 2 min

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

TheSQLGuru (10/28/2009)
I would think that doing 4 table scans and 4 joins on a table with 40M rows WILL take some time. 🙂

I thought 40 Lakh = 4 Million ?

Anyway either way it is still not going to run fast with all those unions and joins

John Rowan SSC Guru Points: 56440 More actions · Answer 2

steveb. (10/26/2009)
You will need the correct indexes to speed up the query.
Can you post the table and index structure as well as your query and execution plan?

We're still waiting on your indexing information and execution plan details.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mssdprasad04 SSC Enthusiast Points: 171 More actions · Answer 3

Hi John iam attaching execution plan pls go through it.

John Rowan SSC Guru Points: 56440 More actions · Answer 4

What about your indexing information for all of the tables involved?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

There really was no need for indexing information or the query plan. 🙂 The query as written clearly was doing 4 table scans and 4 associated joins. Even with covering indexes this is going to be suboptimal. I believe there is a way to rewrite the query to be single pass. I presented one possible method and there are two other potentially viable mechanisms I can think of if the first (and simplest) doesn't work.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Yep, SQLGuru is absolutely correct.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mssdprasad04 SSC Enthusiast Points: 171 More actions · Answer 7

Hi Sql Guru I tried with example which u gave but it is much time than what i already had can u give me those two possible

solutions

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

mssdprasad04 (10/28/2009)
Hi Sql Guru I tried with example which u gave but it is much time than what i already had can u give me those two possible
solutions

I am swamped today so I won't be able to get to it until tomorrow.

In the mean time, please give us the query plan for my first attempt. Also please attach the output you get from SET STATISTICS IO ON from your original query and my version.

Oh, and does mine give equal output as yours?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service