[dba-VB] vb.net - Updates

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Wed Apr 30 16:00:45 CDT 2008



<<<
Again, that is in SSMS, directly 
running a query and "displaying" it in a query pane.  
Was that your intention?
>>>
Yes, it was, but I expected you'll get results much quicker...

Could that be that you do not have (clustered)index on your PK field?

Can you: Right-Click on table name -> Script Table as -> CREATE to -> New
Query Window => I'm getting the following for your simulated table.

CREATE TABLE [dbo].[Names](
	[PKID] [int] IDENTITY(1,1) NOT NULL,
	[OWNERNAME] [nvarchar](255) NOT NULL,
	[FName] [nvarchar](50) NULL,
	[MName] [nvarchar](50) NULL,
	[LName] [nvarchar](50) NULL,
	[NamePrefix] [nvarchar](50) NULL,
	[NameSuffix] [nvarchar](50) NULL,
	[Gender] [nvarchar](50) NULL,
	[ParsedName] [nvarchar](255) NULL,
 CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED 
(
	[PKID] ASC
)
WITH (PAD_INDEX  = OFF, 
STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Also if you select query's text in SSMS query window, and then Ctrl+L then
you'll get query execution plan - here as the last step with 99% cost I'm
getting: "Clustered Index Scan" IOW the query doesn't scan table's data at
all - it just scans the index...

Thanks,

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, May 01, 2008 12:19 AM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] vb.net - Updates

Shamil,

 > You have a very good PC, lucky man! :)

As I mentioned elsewhere, I come from the "throw more hardware at it" 
school of Engineering.  ;-)

I have another machine (Azul) with a quad core (AMDPhenom) 2.2ghz, 8 
gigs ram, Win2k3 x64 SQL Server 2005 x64.  That machine is MUCH faster 
in many cases.

I do not have this large database over on that machine, and it would 
take me hours to get it over there so i can't test this query on that, 
at least today.  that database has not been compacted recently and is 
about 300 Gigs with 136 gigs free space.  I will compact it tonight and 
then copy the smaller file to Azul.

As for your new query, with the NoLocks in place it returned first 
results in only 8 seconds as opposed to 14 before, but it still took 
13:59 to pull the entire result set.  Again, that is in SSMS, directly 
running a query and "displaying" it in a query pane.  Was that your 
intention?

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Thank you, John,
> 
> You have a very good PC, lucky man! :)
> 
> As I wrote already I (I'm sorry) did post incorrect query - I wanted to
run
> this one:
> 
> SELECT RowNum, PKID from  ( select  
> 	Cast(ROW_NUMBER() over (ORDER BY [PKID]) as int) as RowNum, PKID
>   from [Names]) s  
> 	where (s.RowNum % 1000) = 1
> union all
> Select NULL as RowNum, Max(PKID) as PKID from [Names]
> 
> It will also return 96K rows for your huge table, but it's expected (here)
> it will get first results instantly, and it will finish within just
several
> (3?) seconds...
> 
> Please prove am I wrong with that bright above expectations on the
execution
> time of the fixed version of the query?...
> 
> Thank you.
> 
> --
> Shamil
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, April 30, 2008 9:41 PM
> To: Discussion concerning Visual Basic and related programming issues.
> Subject: Re: [dba-VB] vb.net - Updates
> 
> Shamil,
> 
> It took Stonehenge (2.8ghz AMD x2, 4 gigs Ram, Windows x32, SQL Server 
> x32) 13:51 to pull 96,417 rows for the result set.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Shamil Salakhetdinov wrote:
>> Hi John,
>>
>> May I ask you to run this query (e.g. in MS SQL Management Studio)
against
>> your huge db table?
>>
>> --- cut here ---
>>
>> SELECT RowNum, PKID from  ( select  
>> 	Cast(ROW_NUMBER() over (ORDER BY [PKID]) as int) as RowNum, 
>> 	PKID, OWNERNAME, FName, MName, LName, NamePrefix, 
>>     NameSuffix, Gender from [Names]) s  
>> 	where (s.RowNum % 1000) = 1 
>> union all
>> Select NULL as RowNum, Max(PKID) as PKID from [Names]
>>
>> --- cut here ---
>>
>> M.B. [Names] have to be substituted with the name of your table - what is
> it
>> BTW?
>>
>> This query returns PKID of every 1000th row sorted by PKID +
MAX(PKID)....
>>
>> I'm curious to know how much time approx. this query will take to
> execute...
>> Thank you.
>>
>> --
>> Shamil
>>
>> P.S. BTW, here is a useful for your case article on multi-threading
>> (practical solution) with good introduction -
>>
>
http://www.dotnetjunkies.com/Tutorial/D7E688B8-0BDD-4D44-9A0F-4CD26FB35F51.d
>> cik 
>>
>> Sorry, I can't react promptly on your messages in this discussion thread:
> a
>> lot of custom work here, long weekends and our time zones difference but
I
>> do continue to work in this direction and if I get some useful  results I
>> will post them here...
>>
>> P.P.S. This book could be also useful:
>>
>> C# Threading Handbook 
>> by Tobin Titus et al.   
>>
>> ISBN:1861008295 
>>
>> APress, LLC C 2004 (288 pages) 
>>
>> This book addresses the fundamental units of Windows and .NET
>> programming-threads. Coverage includes how .NET applications are
executed,
>> the life cycle of a thread in .NET, how the .NET Framework uses threads,
> and
>> more.
>>
>>
>> _______________________________________________
>> dba-VB mailing list
>> dba-VB at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-vb
>> http://www.databaseadvisors.com
>>
>>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com




More information about the dba-VB mailing list