Between Statment only for current day

Question

Between Statment only for current day

Viewing 15 posts - 31 through 45 (of 46 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

jon pill (7/15/2010)
drop table #TodayRecords
Is this the fastest way, i will have over a 100 lines and will be calling sql every 500ms?

Why would you want to run the code 120 times every minute when the data only changes 100 times per day?

Anyway, here's a set of statements which is a mishmash of the contributions so far. The local temporary table is no longer referenced which means dipping into the table twice. Test to see if this is adequate.

DECLARE @MinTtlTrades INT

SELECT @MinTtlTrades = MAX(TotalTrades) - 1000

FROM DBVaskVbid WITH (NOLOCK)

WHERE BarStamp >= dateadd(dd, datediff(dd, cast('1900-01-01' as datetime), getdate()), cast('1900-01-01' as datetime)

SELECT MAX(upvolume),

MAX(downvolume),

MIN(upvolume),

MIN(downvolume)

FROM DBVaskVbid WITH (NOLOCK)

WHERE BarStamp >= dateadd(dd, datediff(dd, cast('1900-01-01' as datetime), getdate()), cast('1900-01-01' as datetime)

AND TotalTrades > @MinTtlTrades

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jon pill SSCommitted Points: 1659 More actions · Answer 2

Thank you very much, I was having trouble with permissions on that query as I connect remotely to SQL

there will be approx 58,000 records a day and be called 2 times a second.

the input will also be twice a second

I will see what happens 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

How long does the query take to run?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

jon pill (7/15/2010)
Really sorry about this,
But I can only run the query once
Msg 2714, Level 16, State 6, Line 1
There is already an object named '#TodayRecords' in the database.
I would like to run this many times a day, how can I clear the object?

Are you going to keep connection open between runs?

I believe not, therefore this problem shouldn't worry you, as temp table life-span is only within connection and it will be dopped automatically. For testing, you can add drop table statement...

Depending on data volume in your real table, using # temp table may help performance.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

jon pill SSCommitted Points: 1659 More actions · Answer 5

I am running the query from Chris, it does not take long, not sure how long but I do notice some delay.

I keep the connection open at the moment, so I would need to change permissions to run temp table, which i don't know how to do

I am looking at how to do it, I hope i can run the other query

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

jon pill (7/15/2010)
I am running the query from Chris, it does not take long, not sure how long but I do notice some delay.
I keep the connection open at the moment, so I would need to change permissions to run temp table, which i don't know how to do
I am looking at how to do it, I hope i can run the other query
thanks

Easiest way if you're using a SQL Server client is

set statistics time on

select GETDATE() -- replace with your query

set statistics time off

then check the messages tab

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jon pill SSCommitted Points: 1659 More actions · Answer 7

SQL Server Execution Times:

CPU time = 32 ms, elapsed time = 23 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 17 ms.

Thats for Chris's Query

And for Eugene

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 2 ms.

SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 57 ms.

(86776 row(s) affected)

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 79 ms.

SQL Server Execution Times:

CPU time = 78 ms, elapsed time = 80 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 8 ms.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

jon pill (7/15/2010)
Really sorry about this,
But I can only run the query once
Msg 2714, Level 16, State 6, Line 1
There is already an object named '#TodayRecords' in the database.
I would like to run this many times a day, how can I clear the object?

You shouldn't have to drop the object called #TodayRecords if the connection is dropped. Temp tables will drop automatically once a session is ended. Where are you getting this message from... SSMS or the application?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jon pill SSCommitted Points: 1659 More actions · Answer 9

SQL messaging

Without drop table I can only run once

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

jon pill (7/15/2010)
SQL messaging
Without drop table I can only run once

Jon, it is something wrong in a way you executing it.

It looks like you have connection open between execution. It doesn't look right! As mentioned by myself and Jeff, temp table is dropped automaticaly when connection closes. What exactly are you using to execute this query?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

jon pill SSCommitted Points: 1659 More actions · Answer 11

SELECT upvolume,downvolume,TotalTrades

INTO #TodayRecords

FROM DBVaskVbid WITH (NOLOCK) -- are you fully awre of what this hint does?

WHERE BarStamp >= '7/14/2010'

-- do you have huge vlume of records per day, than the following index might help

CREATE INDEX idx_TodayRecords ON #TodayRecords(TotalTrades)

DECLARE @MinTtlTrades INT

SELECT @MinTtlTrades = MAX(TotalTrades) - 1000 FROM #TodayRecords

SELECT MAX(upvolume), MAX(downvolume), MIN(upvolume), MIN(downvolume)

FROM #TodayRecords

WHERE TotalTrades > @MinTtlTrades

I run this code from SQL server, New > query, I can run once only, if Drop Table is inserted I can run many times

I also send SQL the query from another program using the same ID that I log in with to SQL, but remotely, I get the error message that I don't have permission to Drop table's.

I don't not drop the Connection after I have opened it,only when i close the app.

From a c# application

using System.Data.OleDb;
using System.Data.SqlClient;
private string ConnectionString = @"data source=SERVER2\SQLEXPRESS;initial catalog=Trader;user id=Jon;password=Jon";
dbconnection =new SqlConnection(ConnectionString);
dbconnection.Open();

Then query

sSQL = "DECLARE @MinTtlTrades INT ";
using (SqlCommand myCommand = new SqlCommand(sSQL,dbconnection))
{
SqlDataReader reader = myCommand.ExecuteReader();
if (reader.Read())
{
if (!reader.IsDBNull(0))volumeupmin=((int)reader.GetValue(0));
}
reader.Close();
}

I only dispose when closing the application

public override void Dispose()
{
base.Dispose();
// clase the db connection at the end
if (dbconnection != null) {
dbconnection.Close();
dbconnection = null;
}
}

Else I am opening and closes many times and does not seem proper to do so

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

jon pill (7/16/2010)
1.
...
I run this code from SQL server, New > query, I can run once only, if Drop Table is inserted I can run many times
...
2.
...
Else I am opening and closes many times and does not seem proper to do so
...

1. While you're testing the query in SQL Management Studio, you will need to drop temp table before each run, so you can have DROP TABLE statement at the end of your query.

2. Why do you think it is not proper to do so? Actually, it is opposite!

Where have you found the information advising you to keep connection open during application life-span?

Good practice working with SQL Server database from client application is to close the connection straight away after your query executes.

Keeping connection open during the application life-span is usually very bad practice.

Have you heard anything about connection pooling?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

jon pill SSCommitted Points: 1659 More actions · Answer 13

I am not a SQL expert just things I have picked up to do simple queries.

I don't know about connection pooling either, but I will read up on it

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

jon pill (7/16/2010)
I am not a SQL expert just things I have picked up to do simple queries.
I don't know about connection pooling either, but I will read up on it

Jon, are you permitted to create/amend stored procedures? What about tables?

Why does the code have to be run twice per second when the returned values will change little in that time?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rparodi SSC Enthusiast Points: 158 More actions · Answer 15

SELECT min(upvolume),min(downvolume),Max(upvolume),Max(downvolume)

FROM

(SELECT upvolume,downvolume,TotalTrades from DBVaskVbid WITH (NOLOCK) where CAST(BarStamp AS DATE()) = CAST(GETDATE() AS DATE())) as XDtable

WHERE TotalTrades

BETWEEN

(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK)) -1000

AND

(SELECT MAX(TotalTrades)FROM DBVaskVbid WITH (NOLOCK))