July 18, 2008 at 12:14 am
Hi all
I had created a table in sql to extract data & do simple calculations.
this data pertains to Indian National Stock Exchange (NSE) futures bhavcopy. It has generally on average 22000-25000 rows with 15 columns.
the same I dump in to sql & extract required output (calculation) in excel now the table size is nearly 15 lac rows & it turned my system so weak that even a single select query takes too much time.
e.g the query is select count(*) from tblfnobhav ( my table name)
it shows me output 1468050 & took 4 minutes 55 seconds & during the period in task manager sqlservr.exe file size grow to 200 mb in memory which literally hangs my PC
pl suggest me how to speed up my table
In the table there is neither any constraint, nor any user defined function, no stored procedures, no foreign key in short nothing is there in table except my row data (even calculation I do in excel after extracting data from sql)
now suggest me what should I do to speed up the query & output time.
pl try to explain in brief & I am newbie to SQL & knows only basic joins, select query & fundamentals nothing about functions, procedures etc etc.
eagerly awaiting for a positive reply
Regards,
Anand M. Bohra
July 18, 2008 at 12:30 am
- 200mb for sqlserver isn't that much.
- How much RAM do you have on your PC ?
- Post the tables DDL (create statement, triggers, indexes,..)
- source : 25000 rows -> target : 1468050 rows ??? Is this what you intended ?
- the execution time of your select count(*) may have suffered locking issues (as well as the paging overhead for sqlserver) if anything else has been running at sqlserver (e.g. a load or process for that table)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 18, 2008 at 12:36 am
Can you post the query please, the table definition, the index definitions and the execution plan?
You get the exec plan by running the query in management studio with the Include actual execution plan' option (on the Query menu). Right click the plan, select save as, save it as a .sqlplan file, zip and attach to your post.
Solving perf problems without that info in very difficult.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 12:38 am
Yes Sir
when I see windows task manager under process tab
image name shows sqlservr.exe under mem usage it goes upto 198,200 K
I have 512 mb ram on my pc & its p4 3 ghz
the source file size is so huge because it contains Indian stock exchanges Futures & Options (F&O) data
I am pasting table coding
CREATE TABLE [tblfnobhav] (
[INSTRUMENT] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NSECODE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXPIRY_DT] [datetime] NULL ,
[float] NULL ,
[OPTION_TYPE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OPENP] [float] NULL ,
[HIGHP] [float] NULL ,
[LOWP] [float] NULL ,
[CLOSEP] [float] NULL ,
[SETTLE_P] [float] NULL ,
[CONTRACTS] [float] NULL ,
[VAL_INLAKH] [float] NULL ,
[OPEN_INT] [float] NULL ,
[CHG_IN_OI] [float] NULL ,
[DATEP] [datetime] NULL
) ON [PRIMARY]
GO
the above table is created by sql itself with this query
SELECT * INTO tblfnobhav
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\Documents and Settings\admin\Desktop\STRIKE OF PAIN CALCULATOR.xls', 'SELECT * FROM [fobhav$]')
any other requirement from my side pl let me know
July 18, 2008 at 12:50 am
Index definitions and execution plan please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 12:52 am
apart from row data as I told there is nothing in the same like Index or procedures or whatever.
& just now I put one simple query to extract the max date i.e select max (datep) from tblfno bhav & it took nearly 2 minutes
pl find attached file showing execution plan (screen image as save option was disabled)
July 18, 2008 at 1:05 am
Thanks for mentioning that you were running on SQL 2000.
That particular query would be helped by an index on datep, however I thnk you have bigger problems than one query.
Is there anyone there that knows anything about indexs, how and on what to create them? Tables with a million or more rows and no indexes are going to be slow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 1:20 am
May I just add to avoid any N-typed datatype.
If you don't actualy need it, use varchar in stead of nvarchar.
We have recently had an issue where the same query on an equal table
one using nvarchar, the other using varchar. (indexes same indexes on both objects)
Results :
Nvarchar version:
Table 'Protocol'. Scan count 4, logical reads 24, physical reads 0, read-ahead reads 0.
Table 'Order'. Scan count 18, logical reads 183, physical reads 0, read-ahead reads 0.
Table 'TypeAddress'. Scan count 6, logical reads 18, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 236879, logical reads 370838, physical reads 0, read-ahead reads 0.
Table 'Patient'. Scan count 1, logical reads 2554, physical reads 0, read-ahead reads 0.
Table 'Request'. Scan count 1, logical reads 5680, physical reads 0, read-ahead reads 0.
Table 'DoctorAddress'. Scan count 1, logical reads 68, physical reads 0, read-ahead reads 0.
Table 'Doctor'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3610 ms, elapsed time = 8142 ms.
Varchar version:
Table 'Protocol'. Scan count 4, logical reads 24, physical reads 0, read-ahead reads 0.
Table 'Order'. Scan count 18, logical reads 183, physical reads 0, read-ahead reads 0.
Table 'TypeAddress'. Scan count 6, logical reads 18, physical reads 0, read-ahead reads 0.
Table 'Request'. Scan count 1, logical reads 4157, physical reads 0, read-ahead reads 0.
Table 'Doctor'. Scan count 1781, logical reads 7383, physical reads 0, read-ahead reads 0.
Table 'DoctorAddress'. Scan count 1, logical reads 51, physical reads 0, read-ahead reads 0.
Table 'Patient'. Scan count 1, logical reads 1749, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1047 ms, elapsed time = 3347 ms.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 18, 2008 at 1:44 am
ALZDBA (7/18/2008)
We have recently had an issue where the same query on an equal table
one using nvarchar, the other using varchar. (indexes same indexes on both objects)
Implicit conversion issues? I've been bitten very badly in the past by those.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 1:51 am
GilaMonster (7/18/2008)
Thanks for mentioning that you were running on SQL 2000.That particular query would be helped by an index on datep, however I thnk you have bigger problems than one query.
Is there anyone there that knows anything about indexs, how and on what to create them? Tables with a million or more rows and no indexes are going to be slow.
thanks I tried in enterprise manager & able to make this index
CREATE
INDEX [Indx_datep] ON tblfnobhav ([DATEP] desc )
& bravo the same query return value in 1 second
thanks to you & all who devoted their precious time in reading & solving my query
July 18, 2008 at 3:17 am
GilaMonster (7/18/2008)
ALZDBA (7/18/2008)
We have recently had an issue where the same query on an equal table
one using nvarchar, the other using varchar. (indexes same indexes on both objects)
Implicit conversion issues? I've been bitten very badly in the past by those.
Yep. Time and again causing issues because everybody forgets to declare the variables correctly, not to mention to put the N before a hardcoded value. where col1 = N'abc'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 18, 2008 at 4:37 am
My favorite was a java-based messaging app that passed all string literals as N'... despite defining the table with varchar columns.
Net result, index scan of around a million rows to get 1 row, running about once a second.
Vender said it was a fault of SQL server that the app didn't scale and deadlocked often. Hmmmm...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 5:48 am
GilaMonster (7/18/2008)
My favorite was a java-based messaging app that passed all string literals as N'... despite defining the table with varchar columns.Net result, index scan of around a million rows to get 1 row, running about once a second.
Vender said it was a fault of SQL server that the app didn't scale and deadlocked often. Hmmmm...
Lovely :w00t:
I bet this vendor also told you :
- you're the only one having this issue
- you're the first one complaining / reporting this issue
- probably your disc configuration isn't what it is supposed to be
- you (the dba) must have messed around with indexes
- "who told you to put tempdb on another disk"
- our dev-teams only deliver state of the art applications... must be your config
- "yes, but if you had listend at install time, the application would be using the SA user and would accomplish this without a flaw"
:w00t::hehe::P:D
It just proves everyone encounters it, but not everyone wants to admit it is in her/his own hands to correct it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 18, 2008 at 6:37 am
Some of those sound familiar.
The table in question also had 34 NC indexes, all with the same leading column, 80% with the same second column. The leading column of all of the indexes was never used in a query and only had 1 value in the entire table.
Among the gems I remember:
"Our indexing strategy is optimal and works perfectly on Oracle and DB2" (yeah, right)
Deadlocks have nothing to do with locking (while I was trying to explain why the app is deadlocking so often)
Frequent deadlocks are caused by a well known bug in SQL server. Contact their support people (and the kb article of said well known bug is ???)
You haven't got a clue what you're talking about! (Said to me in a meeting that included most of IT management)
I fixed the indexes in about a week, then changed the column data type to nvarchar a couple weeks later.
Voila, no deadlocks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 7:00 am
GilaMonster (7/18/2008)
You haven't got a clue what you're talking about! (Said to me in a meeting that included most of IT management)
And sweet is the odor of victory 😎
But sometimes no one acknowleges it ... "after all you just did your job".
Kind of like peeing in your pants, when you're wearing a black suit.
Nobody notices it, but it gives you a warm feeling
😀
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply