LiveCode Forums.

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

Hi John,

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

Kind regards,

Mark

Mark,

Try what???


John

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

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

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

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

Hi John,

You're running your query twice. Just remove
and all should be fine.

Kind regards,

Mark