Query with SELECT and SUBSTRING

Hello,
I'm a beginner in SQLServer and I'm trying to crite a query with a subst
ring but without success. I've got a field (String) in a table which con
tains a price formatted like that "AUD 2,000.10". I would like with a su
bstring (or something else) obtain something like "2000.10". Can somebod
y help me with that ?
Thanks a lot
Vincent
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Article poste via Voila News - http://www.news.voila.fr
Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP 3500
978]Hi Vincent,
Is it always AUD ?
try
select convert(money,right(col1,len(col1)-3))
I hope this helps
--
Greg O
http://www.sql-scripts.com
"MOTTE" <liste@.france-dev.com> wrote in message
news:c4agh9$src$1@.news.x-echo.com...
> Hello,
> I'm a beginner in SQLServer and I'm trying to crite a query with a subst
> ring but without success. I've got a field (String) in a table which con
> tains a price formatted like that "AUD 2,000.10". I would like with a su
> bstring (or something else) obtain something like "2000.10". Can somebod
> y help me with that ?
> Thanks a lot
> Vincent
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Article poste via Voila News - http://www.news.voila.fr
> Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP
3500978]

Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:
>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query Timout

Hi All,
I am fairly new to SQLServer, although I have used other RDBMS software
extensively.
Is there a simple way to restrict the amount of time a query runs for?
I have inherited a system that allows users to run very large queries and
it is killing performance. I want to time the query out after a given
number of second.
TIA
PeterPeter,
serverside you could look at setting the query governor cost limit using
sp_configure (see BOL). It will disallow 'costly' queries from starting so
isn't exactly what you are referring to but you might still find it useful.
On the client side, the sqlcommand object (or its equivalent in your
programming language) should have a query timeout property.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in news:ODiQnZ$fGHA.5104
@.TK2MSFTNGP04.phx.gbl:

>
Thanks Paul,
I'll use this as a work around. We do not have the source for the
application unfortunately.
Peter

Query timeout when rows returned < TOP (n)?

SQLServer 2005, ~7 million records, queries are using a clustered index keyed on the field "date".

Both queries below have the same execution plan, IO Cost, etc but Query 1 takes ~38 seconds whereas Query2 takes ~.3 seconds.

The only difference is in one of the where clauses (point=). It seems to have something to do with the fact that the first query is only returning 66 rows, but I'm at a loss as to why it's so slow. Query 1 is sub 1 second if I do a select top 66, but ~38 seconds with a top 67.

Obviously there's something I'm missing, but I'm completely clueless as to what it is.

Thanks - James

Query 1:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

37759 ms

66 rows

IO Cost: 188.225

Returns rows 1-61 < 1 second, 62-66 @. ~38 seconds

Query 2:

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 1')

ORDER BY date DESC

334 ms

100 rows

IO Cost: 188.225

It really depends on how many records there are of Attenna 1 and Antenna 2.

Clearly out of all your records there are only 66 that match Atenna 2 so it probably had to look through every record taking 38 seconds. however, there seem to be a whole lot more Attenna 1 or they were toward the begging of your records. Because it filled the Top 100 you specified. Once that is filled there is no point for the query to keep executing and it popped back after only 3 seconds.

On top of that your index is no on any of the columns in your where clause. An index is not a magic item. You indexes on your WHERE criteria in order for it to take advantage of it.

|||

Query plans are your friend.

Look at the query plan and you will see the difference between both queries.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Yep query plans are the same for both. I've got indices for the other fields also.

The odd thing is with a if I give it a point name that doesn't exist like

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'xx')

ORDER BY date DESC

It uses the index for point then hits the clustered index, but the same query with a valid point name

SELECT TOP (100) id, org, zone, point, date, eventType, data, dataName

FROM history

WHERE (org = 'Testbed') AND (zone = 'Reader202') AND (point = 'Antenna 2')

ORDER BY date DESC

it uses only the clustered index. I'm starting to wonder if it might be a problem with Stastics being out of synch for some reason.

Thanks again - James

|||

Query 1 required a scan of 100% of the table. Even after looking at the whole table, only 66 records were returned.

In the second query, it found 100 records very quickly so there was no need to continue.

If you were to remove the "Top 100" from these queries, the execution times would be very similar as both queries would be required to scan the whole table (with this caviat: If Query 2 returne 200,000,000 records, it's going to take longer...the table scan won't take longer to locate the records, but actually reading the disk and moveing the bits will take longer).

Query Timeout

Hi there,
I have a java application which connects to an sqlServer database and issues
an SQL update statement.
When you attempt to connect to the database if there is no connection then
this is fine, it will retry for x times and timeout at the time you have
set.
The problem is if you have made a valid connection and then disconnect the
database from the network and try to run the sql UPDATE. The update will
only attempt one time and basically hang until the connection is
re-established.
The other way round, if the machine the java application is running on is
disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
times and times out at the interval set.
It only seems to hang if the java application is still connected but the
database is disconnected. Is this a general problem with the jdbc driver?
Regards
Jamie
Jamie wrote:

> Hi there,
> I have a java application which connects to an sqlServer database and issues
> an SQL update statement.
> When you attempt to connect to the database if there is no connection then
> this is fine, it will retry for x times and timeout at the time you have
> set.
> The problem is if you have made a valid connection and then disconnect the
> database from the network and try to run the sql UPDATE. The update will
> only attempt one time and basically hang until the connection is
> re-established.
> The other way round, if the machine the java application is running on is
> disconnected it works fine. i.e. it attempt to send the UPDATE query fro x
> times and times out at the interval set.
> It only seems to hang if the java application is still connected but the
> database is disconnected. Is this a general problem with the jdbc driver?
> Regards
> Jamie
It's at a lower level than that. The failure you cause means the TCP stack will
take minutes before it notifies the driver that the socket is dead. You might
try setting the query timeout on your statement before executing it. Then the
driver may be able to return control to you sooner.
Joe Weinstein at BEA