cache, mssql and cursors
kevin furze
minutes and get the latest additions to the remote server, pick out
certain fields and then store just those fields over in cache
I am saving how far I'vee got in the last loop, and then when I call
it ten minutes later, I increment the range and fetch again.
Over time, the remote database will become very large, and in order to
do historical analysis, I really need the data on my local machine in
cache. to save on traffic.
I realise from the sql select statement, I can use the "TOP ?" and the
"LIMIT ? ?" command, so if this is the simple answer, I am happy to do
it, but so far, even that hasn't worked
I have linked cache to the remote database, and can happily connect to
it, retrieve data etc
when I then try to filter the data to the top 100 entries, I get
SQLCODE=-400
If I put the same select statement into the "Execute SQL function",
without changeable variables, it happily runs and produces the top 100
entries back into the browser
set startFrom=0 , stop=100
&sql(DECLARE curs1 CURSOR FOR
select ID, Location
from SatNav.GpsAddress
where ID>:startFrom and ID<:stop)
&sql(OPEN curs1)
FOR {&sql(FETCH curs1)
q:SQLCODE
; store the values into cache
&sql(CLOSE curs1)
when I run it, an SQLCODE of -400 is returned and no matter what I do,
I can't get past the fetching of the cursor.
when I look at the error, I get
SQLState: (08S01) NativeError: [0] Message: [Microsoft][SQL Native
Client]Communication link failure
I have no way of amending the database, its in Dutch and remote, I'm
only enquiring, putting it into cache for more
extensive analysis later on
so of course, the question is, "where am I going wrong" - is it the
cursor, is it variables, any answers please.
( I am away from the desk for part of tomrrow, but will try to dip in
and out of the thread if time permits.
kev
Sukesh Hoogan
A) Check the SQL Gateway log
Set ^%SYSLOG = 3
http://127.0.0.1:1972/csp/docbook/DocBook.UI.Page.cls?KEY=GSQG_C13365
B) Restrictiins
http://127.0.0.1:1972/csp/docbook/DocBook.UI.Page.cls?KEY=GSQG_C13374
C) Have you tried to access the tables using a Stored Procedure?
D) Have you tried something like this ?
INSERT INTO ClassName (Prop1,Prop2,...) SELECT Col1, Col2,.... FROM
TableName WHERE ID BETWEEN X AND Y
Regards
Sukesh Hoogan
Bombay, India
[Enterprise Resource Planning & Business Intelligence]
kevin furze
Long time no chat.
I'll add the tracking flag to see where that leads me, and have a
look at the restrictions,
I cannot use the insert clause, its a readonly database - its actually
receiving GPS/Satnav tracking from vehicles telling me where they go
through the night. I need to be able to enquire every five minutes or
so to allow me to monitor progress, send emails etc.
I'll also try to follow up on the stored-procedures.
kev
kevin furze
I have no choice but to connect to a "ms sql server 2000" - thats what
the dutch server runs, but when I try to "overload" it with fast cache
calls, it gives up with a fatal -400 error code
Once it's crashed, then subsequent calls says "sorry, connection is
still dealing with a previous request"
I am running "Microsoft SQL Server ODBC Driver Version 03.85.1117"
client connection and if I run a test, it happily connects.
I then use the same odbc connection within MS-Access 2000 and I can
happily run connections and obtain masses of data. - it seems that the
problem is connecting with cache via ODBC to my ms sql server,
it seems crazy to have to run a ms-access programme to fetch the
latest data, put it into a small table and then feed THAT across to
cache.
Sukesh Hoogan
Some thoughts
A) The problem may be due to the fact you are using cursors, which is an
expensive resource to use, as their use may involve round trips.
B) While using a Stored procedure, you can use While loop to process
each row retrieved.
C) Instead of passing the IDs as the parameters to a non-cursor query,
you may incorporate them in the statement itself
"SELECT whatever FROM Table WHERE ID="_starFrom_" AND ID<"_stop
The better syntax is
".......WHERE ID BETWEEN "_startFrom_" AND "_stop
Regards
Sukesh Hoogan
Bombay, India
[Enterprise Resource Planning & Business Intelligence]
Sukesh Hoogan
You must also be aware that it is best to use the ANSI SQL syntax when
talking to one DB from another.
Regards
Sukesh
Bombay, India
[Enterprise Resource Planning & Business Intelligence]
Mark Sires
Which version of Cache are you using. Since you indicate that in the
'execute SQL function' without the variable substitution, it works in the
browser, then the problem is either the embedded SQL processing, or a
problem with the variable substitution in the embedded SQL.
I would try using the %Library.ResultSet() class instead, as I would guess
that is what the system manager SQL execute is using:
Set startFrom=0,stop=100
set query= %Library.ResultSet()
set ok=query.Prepare("select ID, Location from SatNav.GpsAddress where
ID>"_startFrom_" and ID<"_stop)
If 'ok quit ;some error - more elegant error logging would be
appropriate....
set ok= query.Execute()
If 'ok quit ;some error
While (query.Next()) {
;store data into cache using query.Data("ID"), etc
}
k query
Mark
"kevin furze" <ke...@oakbeam.co.uk> wrote in message
news:a94f1f4e-d32d-4db7...@l28g2000prd.googlegroups.com...
Mark Sires
set query=%Library.ResultSet().%New()
should be:
set query=##class(%Library.ResultSet).%New("%DynamicQuery:SQL")
Mark
"Mark Sires" <msi...@geeteeeee.net> wrote in message
news:47fe3...@info2.kinich.com...
> Kevin,
> Which version of Cache are you using. Since you indicate that in the
> 'execute SQL function' without the variable substitution, it works in the
> browser, then the problem is either the embedded SQL processing, or a
> problem with the variable substitution in the embedded SQL.
> I would try using the %Library.ResultSet() class instead, as I would guess
> that is what the system manager SQL execute is using:
>
> Set startFrom=0,stop=100
> set query= %Library.ResultSet().%New()
kevin furze
Current version of cache is 2007.1.1.420.0 - reasonably up to date,
I would traditionally use the between range on the sql syntax, but
because the original fetch failed, it seemed a bit pointless to
optimize it (although having written this statement and thought about
it, it's probably not pointless)
Mark, Tonight, I'll have a go at the result set, to see how I get on
with that, - good idea.
Like all of these things, you take the easy option, and then if that
doesn't work,, you move on to others.
thanks for the suggestions anyway.
I'll more than likely be back later tonight.
kev
kevin furze
a quick cut and paste, the odd tweak, and its up and running, not sure
why the other version failed, probably won't even bother to find out
why, I'll just refine this version and brush up on the ANSI SQL
syntax
so for other people wanting a quick cut and paste.
Set startFrom=0,stop=100
where ID>"_startFrom_" and ID<"_stop)
If 'ok quit ;some error - more elegant error logging would be
appropriate....
set ok= query.Execute()
If 'ok quit ;some error
While (query.Next()) {
;store data into cache using query.Data("ID"), etc
}
k query
Mark Sires
In general I have been abandoning embedded SQL in favor of the %ResultSet
class - The queries seem to run better, and it isn't prone to the problems
I've encountered with embedded SQL, particularly cursors. Changing a 'row'
that is in the set of rows in your cursor was generally a bad bad thing,
even if the field changed wasn't in the scope of the where clause. Since
the %ResultSet seems to always retrieve the list of rows into a temporary
global, changing one of them doesn't have the same impact. Of course, if
you have a BIG set of id's returned, the %ResultSet can have its own
problems.
Mark
"kevin furze" <ke...@oakbeam.co.uk> wrote in message
news:8a1ed19d-cd55-49c2...@i36g2000prf.googlegroups.com...
kevin furze
you are saying that the &sql( . . . ) is generally not as good, but
when do you use the &sql( ) and when the %ResultSet ?
is it down to the amount of data coming back at you ? - In the past
I've used the sql builder to work out quickly what I need then just
embedded the statements, adding the "into . . " clause and then
playing with that. - it saved all that concatenation and working out
embedded quotes etc.
is there any penalty ( performance / time ) to using the Dynamic
ResultSets ? it can't be compiling at source time compared to the
embeded sql
kev
Mark Sires
I don't profess to fully understand what goes on under the covers, but I
have generally found %ResultSet to give better (more reliable) results than
&sql these days. In your particular situation, I think the reason is that
&sql with a cursor is making a call over the ODBC driver each time you do a
fetch, and the cursor based fetch wasn't working over ODBC. Using
%ResultSet with a dynamic query in this case makes a single call over ODBC
and retrieves all 100 rows.
There may be cases where %ResultSet doesn't retrieve the entire query in a
single call, but that seems to be its 'preferred' behavior, so in cases like
yours, where the return set is small, and the cost of each call is high, it
works better.
&SQL with a cursor seems to 'prefer' to walk through the table directly,
rather than pulling the matching row id's into a temporary storage area. In
some cases this is good, in other cases, not so much. If it hasn't chosen a
good index to use in traversing the table (do not use -> in your where
clause, it will always choose poorly), each 'Fetch' can be slow. If the
data changes while it is 'walking', it can cause unexpected behavior. And
if you use another &SQL statement to update the same table while in the
cursor loop, very unexpected behavior can sometimes occur. On a much older
version, I tracked that problem down to the reuse of some variable names in
the generated code if the same table was used in 2 &SQL - they may have
fixed that in subsequent releases. I changed my techniques to always use
the cursor to retrieve the rows to look/modify, and then do any updates
after closing the cursor, so I haven't worried about whether it has been
changed or not. This is also why %ResultSet frequently works better for me,
the generated code basically does what I was doing to retrieve the matching
rows into a temporary storage area.
Another technique I use to avoid some of the runtime penalties (which really
aren't much) of %Resultset is to put frequently used Static (or simple
variable substitution) queries in a class, then use that query instead of
%DynamicQuery. Using your query as an example, this could be the query
class:
Class MyStuff.queries [ Abstract ]
{
Query GPSData(StartValue As %BigInt = 0, StopValue As %BigInt = 100) As
%SQLQuery(CONTAINID = 1)
{
select ID, Location from SatNav.GpsAddress where ID>:StartValue and
ID<:StopValue
}
}
Then to use it :
Set query=##class(%ResultSet).%New("MyStuff.queries")
s ok=query.Execute(0,100)
The rest is pretty much the same.
If you are using the same query over and over again, this is more efficient
because the query is 'precompiled', and the code lives in the routines
generated by the class compilation, not in every routine it is used in.
Also, if you need to tweak the query, you have one place to fix it. Of
course, the downside, if you tweak it, you can break all the places that use
it.
Mark
"kevin furze" <ke...@oakbeam.co.uk> wrote in message
news:9267f69c-c2dc-44d0...@b1g2000hsg.googlegroups.com...
kevin furze
In my particular case, lorries go out every night, and have their GPS
positions transmitted back to base. At the same time, they monitor
tail lift movements, bonnets being opened etc. All of this goes back
to the dutch tracking system.
I've been giving access to that DB via MS SQL and odbc, having said
that, its in dutch and I wasn't given the schema, so its been a fun
time working out what I can and cannot gleem from the DB.
I've now built a Linked class to the DB, removing any columns that are
clearly unusable. A second class has been created complete with much
more efficient indicies back in Cache,
I was (no much better off) trying to create a "heartbeat" type process
that keeps track of the last "action" that successfully transferred to
my Cache Class, then every 5,10 minutes or so, make another call to
the remote database and collect more of the latest information,
I am (in my case), going to be calling the same old sql statements
every time, with only the "last position" being changed in the
subsequent polls. so, thanks to your observations, I'll make an
abstract class to handle it.
Once I have the data in my own Cache system, I can then just make
really efficient use of cache to do the real queries.
kev