Get both Count and the data through single query to reduce time

Question

Get both Count and the data through single query to reduce time

Viewing 11 posts - 16 through 25 (of 25 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

binds wrote:

I can even work with a query instead of view.
Again,I just want to confirm if working with a table runs fast than a view?

That's a "how long is a piece of string" type question. It depends on the data, the view definition, and how you query the table. The best way to know is to try it out.

John

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

I just noticed this was in SQL Server 2008 - not sure if COUNT windowing function was available in that version. There are other ways to include a total count though...but it will depend on the actual query and whether that query can be improved.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

John Mitchell-245523 wrote:

Jeff Moden wrote:
John Mitchell-245523 wrote:
Jeff Moden wrote:
John Mitchell-245523 wrote:
Have you tried getting the row count from the sys.partitions view?
John
In this case, I think that's a bad idea because that may not match the actual number of rows actually being written out.
How so? Maybe in SQL Server 2000, where the row_count column wasn't always up to date, but not in modern versions.
Please cite the official MS document and paragraph that states that sys.partitions is up to date with the true/actual/instantly accurate rowcount at all times. I ask because I double-checked the MS documentation for both sys.partitions and sys.dm_db_partition_stats and they both say the same thing (emphasis is mine)...
rows bigint Indicates the approximate number of rows in this partition.
OK, fair enough. I'm guilty of making the assumption that because experience shows it works better than it used to, it is somehow "fixed". My mistake.
John

I was actually hoping that they had made such a change I didn't know about and that you were right! 😀 Thanks for the honest feedback, John.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 4

binds wrote:

I can even work with a query instead of view.
Again,I just want to confirm if working with a table runs fast than a view?

I would start by trying to optimise the view. Is there a query behind the view? If so have you looked at the execution plan?

Maybe you could paste in the view definition with the execution plan from the query you are getting the count from?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Yes, attempting to optimize the view is the way to go. When I first got the notification for this thread, I though that there is something fishy here. If it is a single table, it should not take six minutes to count "millions of rows". Now that I know that it is a view, I am less surprised. For "bind", who does not really seem to grasp the difference: a view is a stored query, which has to be evaluated every time it is referred to.

Then again, aquick-and-dirty solution may be

SELECT * INTO #temp FROM binds_view
SELECT @@rowcount
SELECT * FROM #temp

That would cut the operation to six minutes. But working with the view may make it even faster.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 6

A very good idea from Erland Sommarskog, you might be able to increase the performance even more if the view needs optimising.

The first thing to try is to paste the query into SSMS and click the "Display Estimated Execution Plan" button with the query selected.

tempsnip

This will show you the estimate execution plan and if it thinks there are any good indexes it will put a green message at the top of the plan.

Missing Index (Impact 64.234%) : CREATE NONCLUSTERED INDEX [<Name of Missing Index....

If you right-click this you can select "Missing Index Details" in the popup menu, which will give you the create script for the index.

If you right-click the execution plan an in the popup select "Show Execution Plan XML", then search for "MissingIndexes" it might list more missing indexes. You should try to create these first and you might find quick way to improve the performance of your query.

If this doesn't work you can paste the execution plan into this thread. I think there is a free tool or online resource that will anonymize the execution plan so you can paste it into this thread without disclosing your database design.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

To be honest: I don't think it is with Bind's realm to optimize the view - but it is an advice (s)he can forward to the manager.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 8

Erland Sommarskog wrote:

To be honest: I don't think it is with Bind's realm to optimize the view - but it is an advice (s)he can forward to the manager.

In the question binds states "My manager really asked me to cut down the running time a lot".

So it looks like it now might be his/her task.

binds SSC Enthusiast Points: 190 More actions · Answer 9

Thanks All.

I will work on optimizing the query.

One last quick question(want to know if below thing works)-

Using my SSIS package, first I will be writing the header data to the text file, which includes the total number of records count from that View. After header data,I will be writing all the records/data to the same text file. So I need to know the count also.

As of now I am calling this view for 2 times, once for a count and another time for getting the records,on the whole (header+records),it's is taking 6+6=12mins

And so,Is there any way that I can fetch both at the same time(so that it can be completed in 6 mins only).

Thanks.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 10

And so,Is there any way that I can fetch both at the same time(so that it can be completed in 6 mins only).

So did you look at my suggestion for a temp table above?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

Erland Sommarskog wrote:

And so,Is there any way that I can fetch both at the same time(so that it can be completed in 6 mins only).
So did you look at my suggestion for a temp table above?

Or mine to use GROUP BY with ROLLUP?

John