Speeding Up ODBC Query

[johnmiller1950]

Greetings All,

I use a program I wrote to access a company database on a server using OBDC. My program creates special reports from the database. If I run my program on the server, it performs the queries very rapidly. However, when we use the program on a desktop connected to the server, the whole process takes anywhere from 10 to 20 times as long. I have been doing some reading about this kind of thing, and it has been suggested that when you do a query, make sure that the query is being handled by the server with only the results being sent to the desktop. Is sounds like the solution I'm looking for, but I'm not sure how to do this. Can this be done from within LiveCode?

You help is greatly appreciated.

John Miller

[Mark]

Hi John,

Just try it and let us know the results. If it doesn't work, maybe we can help.

Kind regards,

Mark

[johnmiller1950]

Mark,

Try what???


John

[Mark]

Hi John,

Try making one long query that does all the sorting, filtering etc. on the server instead of a number of small queries that are processed by LiveCode.

Kind regards,

Mark

[johnmiller1950]

Mark,

I am completely confused. Is this a function of the ODBC Query, or is it a LiveCode scripting issue? What LiveCode command/function would I use to pass such a query? I'm not sure how to proceed.

Thanks,
John

[Mark]

Hi John,

How do you currently retrieve data from the database? Can you post a sample of your code?

Usually, you use the revExecuteSQL command or the revQueryDatabase command. The trick is to write one very long query that does as much as possible instead of writing many short queries and using LiveCode scripts to process the data from the queries.

Kind regards,

Mark

[johnmiller1950]

Mark,

Here is my main query code. How would I compact this into one long query?

Thanks,
John

put "SELECT itemNum,name1,class,cost,name2 FROM icitem" into dbCommand
put revOpenDatabase("ODBC","Company 0",,"UserName","Password") into dbID -- databaseID
put revQueryDatabase(dbID,dbCommand) into rsID
put revDataFromQuery (tab,return,dbID,dbCommand) into XX
revCloseCursor rsID
revCloseDatabase dbID

[Mark]

Hi John,

You're running your query twice. Just remove

Code: Select all

put revQueryDatabase(dbID,dbCommand) into rsID 

and all should be fine.

Kind regards,

Mark