Query which returns only every 5th Row with a Openquery

Hi @.all,
I'm going crazy here.
I need to modify this Query so that it returns only every 5th Row
because I have to generate Chart with that data and it would take to
long to get 50000 rows from the DB (at the moment it takes about 2
minutes only for retrieving the data).
The problem is that I have to use the Openquery command.
the command I use now is like that:
SELECT *
FROM OpenQuery( INSQL, '
SELECT DateTime, identifier,meter, x1, x2, x3, x4, x5 FROM WideHistory
WHERE DateTime >= "2006-05-29 12:00:00"
AND DateTime <= "2006-05-29 19:00:00" and identifier is not null')
I used to try some hints that I found here but they unfortunaly don't
work.
I tried the following which would be perfect for me but it end up with
full processor load for over 10 minutes. (I think the OLEDB provider
just crashed)
SELECT
Meter, x1, x2, x3, x4, x5 ISNULL(
(SELECT MIN(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1,
x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S3
WHERE S3.datetime >= S1.datetime
AND ISNULL(
DATEDIFF(
SECOND,
S3.datetime,
(SELECT MIN(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier,
meter, x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S4
WHERE S4.datetime > S3.datetime)), 10) <= 10),datetime)
AS endtime
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1, x2,
x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >= "2006-05-29
17:00:00" AND DateTime <= "2006-05-29 19:00:00" and identifier is
not null') AS S1
WHERE ISNULL(
DATEDIFF(
SECOND,
(SELECT MAX(datetime)
FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter,
x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
"2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
identifier is not null') AS S2
WHERE S2.datetime < S1.datetime),
S1.datetime),
10) <= 10)
Thanks for any help or hint.
Btw: I'm using C# for the frontendHi there,
here is example of the result from the above query:
DateTime identifier meters x1 x2 x3 x4
2006-05-29
17:00:00.000 rollcode901234filmcode901234ref<1678 41213.57421875 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.063 rollcode901234filmcode901234ref<1678 41216.11328125 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.267 rollcode901234filmcode901234ref<1678 41218.14453125 233.0 234.0
235.0 236.0
2006-05-29
17:00:00.467 rollcode901234filmcode901234ref<1678 41218.14453125 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.563 rollcode901234filmcode901234ref<1678 41220.07421875 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.767 rollcode901234filmcode901234ref<1678 41222.20703125 234.0 235.0
236.0 237.0
2006-05-29
17:00:00.967 rollcode901234filmcode901234ref<1678 41222.20703125 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.063 rollcode901234filmcode901234ref<1678 41224.64453125 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.280 rollcode901234filmcode901234ref<1678 41227.18359375 235.0 236.0
237.0 238.0
2006-05-29
17:00:01.467 rollcode901234filmcode901234ref<1678 41227.18359375 236.0 237.0
238.0 239.0
2006-05-29
17:00:01.577 rollcode901234filmcode901234ref<1678 41229.11328125 236.0 237.0
238.0 239.0
2006-05-29
17:00:01.767 rollcode901234filmcode901234ref<1678 41231.75390625 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.063 rollcode901234filmcode901234ref<1678 41233.88671875 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.280 rollcode901234filmcode901234ref<1678 41236.52734375 237.0 238.0
239.0 240.0
2006-05-29
17:00:02.467 rollcode901234filmcode901234ref<1678 41236.52734375 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.563 rollcode901234filmcode901234ref<1678 41238.86328125 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.767 rollcode901234filmcode901234ref<1678 41240.89453125 238.0 239.0
240.0 241.0
2006-05-29
17:00:02.967 rollcode901234filmcode901234ref<1678 41240.89453125 239.0 240.0
241.0 242.0|||Where is the database that you are accessing via open query?
Ideally, you want to do as much of the processing as possible on the source
database. SQL Server can only do so much tuning when it is querying a table
from another database, and it gets much worse when you include more than one
remote table.
You may be much better off building this query on the remote database, or
selecting all the rows and letting C# filter so only every 5th row is kept.
<benwilliams269@.gmail.com> wrote in message
news:1149149017.711607.281380@.i40g2000cwc.googlegroups.com...
> Hi @.all,
> I'm going crazy here.
> I need to modify this Query so that it returns only every 5th Row
> because I have to generate Chart with that data and it would take to
> long to get 50000 rows from the DB (at the moment it takes about 2
> minutes only for retrieving the data).
> The problem is that I have to use the Openquery command.
> the command I use now is like that:
> SELECT *
> FROM OpenQuery( INSQL, '
> SELECT DateTime, identifier,meter, x1, x2, x3, x4, x5 FROM WideHistory
> WHERE DateTime >= "2006-05-29 12:00:00"
> AND DateTime <= "2006-05-29 19:00:00" and identifier is not null')
> I used to try some hints that I found here but they unfortunaly don't
> work.
> I tried the following which would be perfect for me but it end up with
> full processor load for over 10 minutes. (I think the OLEDB provider
> just crashed)
> SELECT
> Meter, x1, x2, x3, x4, x5 ISNULL(
> (SELECT MIN(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1,
> x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S3
> WHERE S3.datetime >= S1.datetime
> AND ISNULL(
> DATEDIFF(
> SECOND,
> S3.datetime,
> (SELECT MIN(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier,
> meter, x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S4
> WHERE S4.datetime > S3.datetime)), 10) <= 10),datetime)
> AS endtime
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter, x1, x2,
> x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >= "2006-05-29
> 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and identifier is
> not null') AS S1
> WHERE ISNULL(
> DATEDIFF(
> SECOND,
> (SELECT MAX(datetime)
> FROM OpenQuery( INSQL, 'SELECT datetime,identifier, meter,
> x1, x2, x3, x4, x5 FROM Runtime.WideHistory WHERE DateTime >=
> "2006-05-29 17:00:00" AND DateTime <= "2006-05-29 19:00:00" and
> identifier is not null') AS S2
> WHERE S2.datetime < S1.datetime),
> S1.datetime),
> 10) <= 10)
>
> Thanks for any help or hint.
> Btw: I'm using C# for the frontend
>|||Here is a rewrite of your query, set to run only against the original
database. See how long it takes to run when run directly against the
source. You may need to tweak some of the syntax, depending on what type of
database you are dealing with. I think the speed will be much, much better
this way.
However, if you want to get every 5th record, I suggest looking into various
paging/ranking techniques to assign row numbers and then retrieve every 5th
row. Try this link to start:
http://www.aspfaq.com/show.asp?id=2427
SELECT
S1.Meter
, S1.x1
, S1.x2
, S1.x3
, S1.x4
, S1.x5
, ISNULL
(
(
SELECT min(S3.datetime)
FROM Runtime.WideHistory AS S3
WHERE S3.DateTime >= "2006-05-29 17:00:00"
AND S3.DateTime <= "2006-05-29 19:00:00"
and S3.identifier is not null
and S3.datetime >= S1.datetime
AND ISNULL
(
DATEDIFF
(
SECOND,
S3.datetime,
(
SELECT MIN(S4.datetime) as datetime
FROM Runtime.WideHistory AS S4
WHERE S4.DateTime >= "2006-05-29 17:00:00"
AND S4.DateTime <= "2006-05-29 19:00:00"
and S4.identifier is not null
and S4.datetime > S3.datetime
)
), 10
) <= 10
),datetime
) AS endtime
FROM Runtime.WideHistory AS S1
WHERE S1.DateTime >= "2006-05-29 17:00:00"
AND S1.DateTime <= "2006-05-29 19:00:00"
and S1.identifier is not null
and ISNULL
(
DATEDIFF
(SECOND,
(
SELECT MAX(S2.datetime) as datetime
FROM Runtime.WideHistory AS S2
WHERE S2.DateTime >= "2006-05-29 17:00:00"
AND S2.DateTime <= "2006-05-29 19:00:00"
and S2.identifier is not null
and S2.datetime < S1.datetime
),S1.datetime
),10
)
<= 10
)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OlAWNyZhGHA.412@.TK2MSFTNGP05.phx.gbl...
> Where is the database that you are accessing via open query?
> Ideally, you want to do as much of the processing as possible on the
source
> database. SQL Server can only do so much tuning when it is querying a
table
> from another database, and it gets much worse when you include more than
one
> remote table.
> You may be much better off building this query on the remote database, or
> selecting all the rows and letting C# filter so only every 5th row is
kept.
>
> <benwilliams269@.gmail.com> wrote in message
> news:1149149017.711607.281380@.i40g2000cwc.googlegroups.com...
>