sp_cursorfetch too many

[baray98]

I made a simple app with QSqlQueryModel and attached it to a QTableView, I connnected it to an MSSQL server and trace it and found that there are millions of sp_cursorfetch traces ( this grows as i move the scroll bar in my view). Since i am not a database guy. i googled it and it says its bad to the network since its fetching a single row at a time and so the whole overhead for the network transfer is added to every singel row.

Can someone enlightened me on this one. How can i make my model not to use sp_cursorfetch?

baray98

[Timoteo]

One approach is to reimplement the model to use a cache-transact technique. Data pulled in from the db is cached locally and operated on, then transacted back to the database. There's a HUGE 'gotcha' here, though. If your application crashes, you lose all of the entry done from the gui. Even worse, is the case where the application doesn't crash and instead posts corrupted data to the database. It all comes down to what your specific case demands.

That being said, the Qt framework is capable of delivering (near) realtime database editing capability and for a lot of applications that is completely appropriate.

[baray98]

My apps doesn't allow any update anyways its a pure viewer for a database.."cache transact sounds like an interesting thing to do" I am wondering if you can elaborate on this idea using qt framework or better yet have a little snippet that i could grasp the idea.

baray98

[Timoteo]

If you're just interested in viewing mostly static data from the database then read on. However, if you are needing to synchronize a lot of updates to your viewer then you are better off assuming that anything you might cache is obsolete already (the behavior that the default model exhibits).

Since you aren't concerned with writing anything back to the database, things get a lot simpler. All you really need is a local sqlite database (on file or in memory - your call) that you write your inbound data from the remote database to. You then just refer to that local database anytime you need the data. So you most likely will not need a new model, just connect the model to your local database and keep it populated with fresh data if/when needed.

What you decide on might end up being a hybrid between caching and fetching. Knowing what to cache and what to always fetch comes down to knowing your specific needs.

[baray98]

i like that idea on caching using sqlite db but the thing is i can not assume the target db is static some other app might be updating it. Now, if I can not assume that it is static then I'm SOL on chaching. I won't gain anything since I have to fetch it everytime.

OR

I'll fetch/update my cache on every refresh on my model now when the view scroll or runs sp_cursorfetch it would be in my cache and no more network overhead. am I going to the right direction?

baray98

[Timoteo]

I would simply cache all of the static data you are interested in (archived data, reference data etc) and simply always fetch the rest. You want to keep the cached set small anyway. It could be argued that caching static data doesn't even gain you much, because the SQL server would have those queries cached already. On the other hand, the only way to spare network traffic is to use a local cache technique. It is a balancing act.

[baray98]

I love to try this out. but i have some implementation problems. I am not sure where to implement my cache

I would simply cache all of the static data you are interested in (archived data, reference data etc) and simply always fetch the rest.

Given an QSqlQueryModel and database table1 has a million rows and with sql statement = SELECT * from table1 where ..limits here...

Where should i discriminate the rows that are not needed in my cache? How can i divert my model to used my cache?

My solution that keeps running in my head is subclass QAbstractTableModel and make my own version of QSqlQueryModel.

your thoughts?
baray98