[dba-VB] vb.net - Updates

jwcolby jwcolby at colbyconsulting.com
Wed Apr 30 16:16:08 CDT 2008


I do have the clustered stuff exactly as you show:

 >  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]

Likewise I am getting the same 99% cost clustered index scan.

I have to guess it is something else.  There is a lot of empty space in 
the database file which I will compact out tonight.  It takes hours 
because of the size and when it is doing that I cannot use the db. 
Perhaps I have severe fragmentation or something.  The database size is 
300 GIGS.  And finally just remember that when all is said and done it 
is still a hundred times larger than your database.  No matter how you 
slice it, it has to process more physical disk sectors etc.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> 
> <<<
> 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
> 
> _______________________________________________
> 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