SQL Index Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Index Issue

Author

Topic

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-15 : 15:15:32

Hi I know next to nothing about SQL admin and maintenance but have a large DB table that is important called 'TICKERS' with 212K rows and the following structure

date datetime
ticker nvarchar
open nvarchar
high nvarchar
low nvarchar
volume nvarchar
change nvarchar
time datetime

There is no primary key index. Because this table is very frequent use table I would like to add a Primary Key Index 'ID' column, but the error I get when I open Design in SMSS and add column is 'time out'

1. How is best way to add this Identify Column 'ID' ? To overcome time out issue?

2. Will I need to modify queries to add 'ID' column to all queries to take advantage of high performance index?

3. Do I need to do the 'ID' column in order by 'Date' ? Should I put an index on Date column also? Can the 'ID' be in any order?

4. Any other words of advice or wisdom for me?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-11-16 : 03:36:21

Why an ID column as primary key? Unless it's used by queries, it's unlikely to help performance much, if at all. What you need to do is index the columns that are used in the queries.

Why are the columns nvarchar? Expecting to be storing unicode (non-roman alphabet) in them? What do those columns store?

If you want to add it anyway, do it in code. The management studio gui times out, the query window won't. You can go into the gui designer, make the changes then get the script (there's a script options somewhere on the dialog) and run it in a query window,

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 12:54:43

Can you post the Actual DDL of the Table? If you don't know how, tell us, and we'll show you.

Also, as Gail mentioned, how is the data going to be accessed?

(i.e. What would "WHERE such and such equals"....going to look like?)

and not for nothing, but 212k rows is very little and is probably in memory

Run this and post the results


SELECT
	  COUNT(*)
	, CONVERT(money,(COUNT(DISTINCT([date]))*1.00/COUNT(*))*100)		AS Date_Cardinality
	, CONVERT(money,(COUNT(DISTINCT([ticker]))*1.00/COUNT(*))*100)		AS ticker_Cardinality
	, CONVERT(money,(COUNT(DISTINCT([open]))*1.00/COUNT(*))*100)		AS open_Cardinality
	, CONVERT(money,(COUNT(DISTINCT([high]))*1.00/COUNT(*))*100)		AS high_Cardinality
	, CONVERT(money,(COUNT(DISTINCT([low]))*1.00/COUNT(*))*100)			AS low_Cardinality
	, CONVERT(money,(COUNT(DISTINCT([volume]))*1.00/COUNT(*))*100)		AS volume_Cardinality
	, CONVERT(money,(COUNT(DISTINCT([change]))*1.00/COUNT(*))*100)		AS change_Cardinality
	, CONVERT(money,(COUNT(DISTINCT([time]))*1.00/COUNT(*))*100)		AS time_Cardinality
FROM [TICKERS]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-16 : 19:35:35

quote:
Originally posted by X002548

Can you post the Actual DDL of the Table? If you don't know how, tell us, and we'll show you.

> I Don't know how to post DDL of table...

Also, as Gail mentioned, how is the data going to be accessed?

> The data is accessed on a Sql Server '05 Hosted by Verio.net, the application is a .net architecture with a Flex / Flash Presentation Layer for Charting Tickers and Stock Trades.

(i.e. What would "WHERE such and such equals"....going to look like?)

> each time the web user clicks on a transaction the ticker table send 2 years of data to a flex data model (is that what you wanted?)

and not for nothing, but 212k rows is very little and is probably in memory

Run this and post the results

> Should change the value of the Open, High, Low to decimal?? Right now it's Float / nvarchar...

SELECT
COUNT(*)
, CONVERT(money,(COUNT(DISTINCT([date]))*1.00/COUNT(*))*100) AS Date_Cardinality
, CONVERT(money,(COUNT(DISTINCT([ticker]))*1.00/COUNT(*))*100) AS ticker_Cardinality
, CONVERT(money,(COUNT(DISTINCT([open]))*1.00/COUNT(*))*100) AS open_Cardinality
, CONVERT(money,(COUNT(DISTINCT([high]))*1.00/COUNT(*))*100) AS high_Cardinality
, CONVERT(money,(COUNT(DISTINCT([low]))*1.00/COUNT(*))*100) AS low_Cardinality
, CONVERT(money,(COUNT(DISTINCT([volume]))*1.00/COUNT(*))*100) AS volume_Cardinality
, CONVERT(money,(COUNT(DISTINCT([change]))*1.00/COUNT(*))*100) AS change_Cardinality
, CONVERT(money,(COUNT(DISTINCT([time]))*1.00/COUNT(*))*100) AS time_Cardinality
FROM [TICKERS]

results...
count ticker card date cardinality open_card high_card low_card
188835 0.1986 0.2658 5.8056 6.9717 6.9288
vol_card change_card time_card
42.2363 1.1195 1.2334

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-16 : 19:43:54

Here is script for table, still not quite sure what ddl you want I use 2005 and from what I gather uses SSMS not Ent Mgr (true)?
But let me know.

CREATE TABLE [dbo].[Tickers](
[ticker] [varchar](10) NULL,
[date] [datetime] NULL,
[high] [float] NULL,
[low] [float] NULL,
[open] [float] NULL,
[close] [float] NULL,
[volume] [float] NULL,
[time] [datetime] NULL,
[change] [float] NULL
) ON [PRIMARY]

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-11-17 : 03:27:48

Ok, to do any useful performance tuning, we need more info.

What column (or set of columns) is unique?
What are the most common queries that run against that table?
Are there any existing indexes?

--
Gail Shaw
SQL Server MVP

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-17 : 09:24:41

The most common columns are date and ticker... the pricing and volume information could just be random and it would not matter. I have a non-clustered index on date and ticker allow nulls not identity.
I have about 20 stored procedures that hit the table the most frequent is the Charting and the Updating procedures (getCharts and Update data)... but I have another one that is a GetBollingerBands Stored Procedure (that one is interesting).. that has slowed to about 20 seconds! Though it used to be fast, I guess I messed something up... I have attached the three stored procedures below, one in each Post.

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-17 : 09:26:04

HERE IS STORED PROCEDURE FOR GET CHART...!

[dbo].[Chart2yrs]
(@Symbol varchar(10))
AS
BEGIN
BEGIN TRANSACTION
SELECT Tickers.[date] AS [day]
, Tickers.[open]
, Tickers.[high]
, Tickers.[low]
, Tickers.[close]
, Tickers.[volume]
FROM Tickers
WHERE (DATEDIFF(day, Tickers.[date], GETDATE()) <= 730)
AND (Tickers.Ticker = @Symbol)
ORDER BY date
COMMIT
END

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-17 : 09:28:34

Here is stored procedure for UPDATE one is real time update and the other is end of day update!!

RT ALTER PROCEDURE [dbo].[RtUpdateTkrs]
@Ticker nvarchar(20),
@Date datetime,
@Time datetime,
@Open float,
@High float,
@Price float,
@Low float,
@Volume float,
@Change float
AS
-- NO EXISTS ------------
Declare @RC int
--BEGIN TRAN
BEGIN TRANSACTION
SELECT *
From Tickers
Where [Ticker] = @Ticker And [Date] = @Date
SELECT @RC = @@ROWCOUNT
IF @RC > 0 AND @Price > 0
UPDATE TICKERS
SET [Date] = @Date
, [Time] = @Time
, [Open] = CASE
WHEN @Open=Null THEN @Price
WHEN @Open=0 Then @Price
ELSE @Open
END
, [High] = CASE
WHEN @High=Null THEN @Price
WHEN @High=0 THEN @Price
ELSE @High
END
, [Close] = @Price
, [Low] = CASE
WHEN @Low=NULL THEN @Price
WHEN @Low=0 THEN @Price
ELSE @Low
END
, [Volume] = @Volume
, [Change] = @Change
WHERE ((Ticker = @Ticker) AND ([Date] = @Date))
ELSE IF @RC <= 0 AND @Price >0
INSERT INTO Tickers ([Ticker], [Date], [Time], [Open], [High], [Close], [Low], [Volume], [Change])
VALUES (@Ticker
, @Date
, @Time
, CASE
WHEN @Open=NULL THEN @Price
WHEN @Open=0 THEN @Price
ELSE @Open
END
, CASE
WHEN @High=NULL THEN @Price
WHEN @High=0 THEN @Price
ELSE @High
END
, @Price
, CASE
WHEN @Low=NULL THEN @Price
WHEN @Low=0 THEN @Price
ELSE @Low
END
, @Volume
, @Change)
COMMIT

This is the other one...

ALTER PROCEDURE [dbo].[UpdateTable]
(
@Ticker nvarchar(10)=null,
@Date datetime=null,
@Open float=null,
@High float=null,
@Low float=null,
@Close float=null,
@Volume float=null
)
AS
BEGIN TRANSACTION
SELECT [Date] from TICKERS
WHERE [Date] = @Date and [Ticker] = @Ticker
IF (@@ROWCOUNT = 0) AND (@Low > 0) AND (@High > 0) AND (@Open > 0) AND (@Volume > 0)
INSERT INTO TICKERS(Ticker, [Date], [Open], [High], [Low], [Close], [Volume])
VALUES (@Ticker,@Date,@Open,@High,@Low,@Close,@Volume)
SELECT * FROM TICKERS
WHERE [Ticker] = @Ticker
ORDER BY [DATE]
COMMIT

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-17 : 09:31:45

This is the stored procedure for BBands -- it has become very slow perhaps I changed the index recently but I forgot what it used to be if it was different... this stored proc is two part the main sproc and the 2 functions that it calls.
Here is the main SP...

ALTER PROCEDURE [dbo].[sproc_BollingerBands]
(
@TKR VARCHAR(10)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT ma.date, ma.movavg, ma.movavg+(2*sd.stddev) As BBUp, ma.movavg-(2*sd.stddev) As BBDown
FROM GetMovingAvg(@Tkr) ma INNER JOIN dbo.GetStdDev3(@Tkr) sd ON ma.date = sd.date
RETURN
END

Here are two INLINE TABLE FUNCTIONS...

ALTER FUNCTION [dbo].[GetMovingAvg]
(
@TKR VARCHAR(10)
)
RETURNS TABLE
AS

RETURN
(
SELECT x.[date], AVG(y.[Close]) movavg
FROM Tickers x, Tickers y
WHERE x.[DATE] > (SELECT TOP 1 z.[DATE] FROM TICKERS z WHERE z.TICKER = @TKR ORDER BY z.DATE ASC)+20
AND (DATEDIFF(day, x.[date], GETDATE()) <= 730)
AND x.TICKER = @TKR AND y.TICKER = @TKR
AND x.[DATE] BETWEEN y.[DATE]-20 AND y.[DATE]
GROUP BY x.DATE
)

AND STAND DEVIATION...

This one is a doosey....

ALTER FUNCTION [dbo].[GetStdDev3] (@TKR VARCHAR(10))
RETURNS @results TABLE (
dayno SMALLINT IDENTITY(1,1) PRIMARY KEY
, [date] DATETIME
, [stddev] FLOAT
)
AS BEGIN

DECLARE @min_sysdate DATETIME, @min_tkrdate DATETIME, @rowcount SMALLINT

SET @min_sysdate = DATEADD(DAY,-731,GETDATE())
SET @min_tkrdate = DATEADD(DAY,20,(
SELECT MIN(DATE) FROM TICKERS WHERE TICKER = @TKR))

INSERT @results ([date],[stddev])
SELECT x.[date], ISNULL(STDEV(y.[Close]),0) AS stdev
FROM Tickers x
JOIN Tickers y ON x.[DATE] BETWEEN DATEADD(DAY,-20,y.[DATE]) AND y.[DATE]
WHERE x.[DATE] > @min_tkrdate
AND x.[DATE] > @min_sysdate
AND x.TICKER = @TKR
AND y.TICKER = @TKR
GROUP BY x.[DATE]

SET @rowcount = @@ROWCOUNT

UPDATE @results SET [stddev] = (
SELECT [stddev] FROM @results WHERE dayno = @rowcount-1)
WHERE dayno = @rowcount
RETURN

END

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 12:31:16

The lower the number the better the cardinality


count	ticker card	date card	open_card	high_card	low_card	vol_card	change_card	time_card
188835  0.1986		0.2658		5.8056		6.9717		6.9288		42.2363		1.1195		1.2334

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 12:36:43

well you lost me

You can hae an ORDER BY in a sub select?

And why is @@ROWCOUNT Important to the query?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cjsteury2
Starting Member

14 Posts

Posted - 2009-11-17 : 19:22:19

quote:
Originally posted by X002548

well you lost me

You can hae an ORDER BY in a sub select?

> Apparently... perhaps it's not needed if SELECT TOP 1 DATE will be ordered the proper way without I don't need it

And why is @@ROWCOUNT Important to the query?

> For that one - it was not calculating the Stand Deviation for the last date properly, so I asked a SQL guru to see if he could help me to replace the last date in the STDEV query with the previous value... It seems complicated to me.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources