urgh that does not format well...

basically the query is the same but

cpu goes from 1109 to 48265

reads goes from 78843 to 9400939

writes goes from 0 to 801

duration increases...

OK, done that.

Trace Start2010-11-08 14:29:09.033

SQL:BatchStartingSELECT dbo.funTotalQuotesHhi('08 Nov 2010','01 Nov 2009','30 Nov 2009')

Microsoft SQL Server Management Studio Express - QuerywardbFORTIS-UK\wardb5336542010-11-08 14:29:16.793

SQL:BatchCompletedSELECT dbo.funTotalQuotesHhi('08 Nov 2010','01 Nov 2009','30 Nov 2009')

Microsoft SQL Server Management...

interesting...

this returns the correct number of rows but takes twice as much time as my original query.

funny how queries work differently in different circumstances/tables eh!

Chris Morris-439714 (10/29/2010)

SwePeso (10/29/2010)

SELECTSUM(1) AS Items

FROMdbo.tblHhiQuotes

WHERE sGlDate <= '28 Oct 2010'

AND sEffectiveDate BETWEEN '01 Nov 2010' AND '30 Nov...

erm....

Msg 8155, Level 16, State 2, Line 6

No column was specified for column 1 of 'd'.

:Whistling:

And the results are in !

My query was the slowest (as was expected lol)

most common speed 156ms with a low of 141 and a high of 196

3rd place goes...

you

are

a ...

😀

I did try rollup, that just gave extra records.

I'll give that a read...

haha yes

which is (I think) exactly the same as

;WITH cteSource(Data)

AS (

SELECT 1

...

thats brilliant thanks.

I'm surprised there isnt an alternative version of count in sql server that returns the number of rows like a numrows() function or whatever.

Thanks

Hi Chris,

thats essentially what I'm doing but twice.. im trying to call that sproc from another and copy data into a table from that exec which sql cant do.

Hi Dan,

Thanks for reply, I'm using xp_cmdshell in order to kick off dtsx packs and use php to odbc to informix and perform some complex logic that I really really...

Thanks Oliii!

I had a look at the steps details and found the tickbox to export to table but couldn't see where/how to specifiy what table to output to! I'll have...

oooh I had not realised this! Thanks!

dont suppose anyone has a tutorial at all? if not I'll google for a bit 🙂

Thanks Buddy,

That's really useful!

Still wondering about the 'x rows affected' stuff but this is very helpful 🙂