Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

simple que Expand / Collapse
Author
Message
Posted Thursday, September 13, 2012 3:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
hi,

i have 1 table which has millions of rows, i am doing select * from tablename
and select count(*) from tablename

does this both take same time or different,if different then why,if same then why.

(2) If i define variable global variable in my stored proc doesnt it appear inside another stored proc.(nested)

Post #1358915
Posted Thursday, September 13, 2012 4:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
harri.reddy (9/13/2012)
hi,

i have 1 table which has millions of rows, i am doing select * from tablename
and select count(*) from tablename

does this both take same time or different,if different then why,if same then why.

(2) If i define variable global variable in my stored proc doesnt it appear inside another stored proc.(nested)



Looks like interview questions to me.

What are your answers so far?

#2 looks a bit irrelevant to SQL Server


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1358971
Posted Thursday, September 13, 2012 4:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
harri.reddy (9/13/2012)
(2) If i define variable global variable in my stored proc doesnt it appear inside another stored proc.(nested)


Perhaps because SQL doesn't have global variables?

For the first, why don't you create such a table, run both queries and see which, if either, is faster?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1358976
Posted Thursday, September 13, 2012 5:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
i dont hv millions of rows ,for simple rows like 100,its time i s 0

Post #1358987
Posted Thursday, September 13, 2012 6:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
create one!

try this one:

select top 1000000 1 as a
into #t
from sys.columns c1
cross join sys.columns c2
cross join sys.columns c3
cross join sys.columns c4




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1358997
Posted Friday, September 14, 2012 6:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
ok.

select count(*) taking 0 seconds,and select * from taking 0.08

but why?i dont know that
Post #1359220
Posted Friday, September 14, 2012 6:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Have a look at the execution plan.

Also consider, how much data has to be transmitted and displayed in each case?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1359230
Posted Friday, September 14, 2012 7:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 6:15 AM
Points: 15, Visits: 65
Literally speaking,

a) "Select *" will return the result set , whereas "Count(*)/Count(1)" will return a scalar value and this will result in significance difference in terms of I/O
b) Other fact is, there is no hard & fast rule to judge the performance; everything depends on the many factors like indexes used, statistics update, etc.....


For your other question, you can pass the variable as a parameter. Do you have any concerns by doing so with respect to your scenario?
Post #1359287
Posted Friday, September 14, 2012 9:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
thanks for your response,

for my other question,

it was something ,if i define global variable in sp,will it can be access in inside stored procedure
Post #1359450
Posted Friday, September 14, 2012 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 6:15 AM
Points: 15, Visits: 65
No you can't
Post #1359469
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse