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