Find Highest Number Without Building an Index

[stanlyn]

Hi,

I need to find a way to get the row containing highest sessionid number WITHOUT building an index like I'm doing here. It takes too long to run and I'm trying to improve performance. Thanks! Here is the code...

Select 'rv_Session'
Index On SessionID Tag iSession
Set Order To iSession Desc
Go Top
Locate For Upper(Alltrim(rv_Session.SystemId)) == Upper(Alltrim(gcSystemId))

If Found('rv_Session')
    gnSessionId = rv_Session.SessionID + 1
Else
    gnSessionId = 1
Endif

 

[GriffMG]

Can you not maintain the index on the underlying table?
Or is it on a subset of the data somehow?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

What is rv_Session? Is it a physical table? If so, you only need to create the index once, when you first create the table. You don't need to do INDEX ON each time you open the table. It is enough to do SET ORDER.

Or, is rv_Session a remote view (as its name suggests)? If so, then instead of creating an index, you can use the ORDER BY clause in the code that creates the view (or use the Order By tab in the View Designer). You will then be able to GO TOP to find the hightest ID.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I think the rv_ prefix is forremote view.

Then you need either an index or a MAX( query, but the MAX() query will be as slow as indexing a large number of rows, as it has to read through all data,

It's not the job of a view to add an ID, that should be done on the table level. Using an integer autoinc field would be the simplest solution.

If you want to have a sub-id, ie number order items of each order from 1 to number of items, then your view also should only select the items of one order, indexing a few records happens in a snap. And then you can get the max to determine the number of the next item.

So either of these has to be addressed:
-create IDs within the table, not the view.
-limit the view data to te necessary subset, which then can also be indexed fast. Indeed views are not having permanent indexes and indexing them is a good solution in case your view is making sense by not fetching all data of a table.






Chriss

 

[stanlyn]

Hi,

Can you not maintain the index on the underlying table?
Or is it on a subset of the data somehow?

The tables are SQL and do not have such an index built. I can build it easy enough, if I only knew how to let the remote views use them.

They are not a subset.

Thanks, Stanley

 

[stanlyn]

Hi Mike,

Or, is rv_Session a remote view (as its name suggests)? If so, then instead of creating an index, you can use the ORDER BY clause in the code that creates the view (or use the Order By tab in the View Designer). You will then be able to GO TOP to find the hightest ID.

Yes, they are remote views, and yes that index is being built every time a user signs on. As I understand it, we cannot save an index on a view. As for changing the defination, would that get in the way of other queries to that view that is indexed a different way? Or do you have to build a view for this and a different view for that? How close can I get to using "set order" syntax for views, and I really don't want to maintain a ton of views... What is best practice here?

Thanks, Stanley

 

[stanlyn]

Hi Chriss,

The backend data has a sessionid field that gets populated from a lot of different source with each one having an allocated range of numbers. This is where the systemid is coming from. It would be very helpfull to just linit by systemid and get the max for the sub group. Something like "select systemid, sessionid from session where systemid = 'abcd'"

So I guess my question is how to create the view with nodata on load, and quering whatever whenever without redefining the view. Instead, use a filter, set order, locate...

Thanks, Stanley

 

[Chris Miller]

Indexes should only be created on tables, On views only for a subset of data that's small.

In an MSSQL database, the indexes are used automatically, if available, just like in VFP. You don't SET ORDER TO, you query with an ORDER BY clause or you just fetch MAX(id) and get back one record with that max id number you want.

But more important than that, you don't create Ids in a view, you create them by inserting them into the database, that's not just the best but the only practice making sense for integer sequence IDs, ID creation has to be done on the database level. And then all your problems become meaningless.

If the view mainly is there to add data, then start with a view with WHERE 1=0 clause, not fetching any data. Append BLANK and TABLEUPDATE(), then requery and you have your new ID.


Chriss

 

[Mike Lewis]

Stanley, re your question about getting in the way of other queries that use the view, the easy answer is that you can create separate views for each of those cases. If, at one point, you need to work with data in a given sequence, you would use the view that incorporates that sequence in its ORDER BY clause. If, at another time, you need a different sequence, or no sequence at all, then you just use a different view.

There is no performance penalty in doing this. The data remains exactly the same. The different views are just different ways of looking at the same data.

The other important point about remote views is that they should contain the smallest amount of data that you need for a given task. If, at a given point in your application, you only need records that meet a given condition, the view should incorporate that condition in its WHERE clause. In your case, if you are only using the view to keep track of Session IDs, then the view's field list should only contain the Session ID and nothing else. That way, given a small amount of data, your LOCATE might work quickly enough even without an index.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

So he should be looking for a view that only returns one or no records in this case Mike?
The one being the highest number and the none if there are no records in the view?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Going further, if the only purpose of the view is to determine the highest Session ID, then you only need to retrieve that one value; you don't need any other data in the view.

To do that (assuming you are using the view designer), in the Fields tab, enter MAX(SessionID) under "Functions and Expression", then click the Add button to the right. That's all you have to do. When you open the view, it will contain just that one value.

Griff: You're right. You post crossed with this one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

more important than that, you don't create Ids in a view, you create them by inserting them into the database

No, no, no... We are not talking about the same thing here. What you are talking about is our PK field (primary key) and I do nothing with that and let the database assign it. There can be duplicate sessionids as they only have to be unique when combined with the systemid.

So how is best way to query for a subset? Is there something simpler than "select SessionId from rv_session where SystemId = lcSessionId order by SessionId desc into cursor curResult" and then use curResult? I was trying to avoid the use of this extra layer/cursor. To solve getting the max number this additional cursor is ok as I'm not writing anything back, only reading and is ok.

But for other needs, I'm trying to best understand how its done, like in the case of finding something in the cursor and then needing to change its value and writing it back to the underlying table.

Stanley

 

[stanlyn]

So he should be looking for a view that only returns one or no records in this case Mike?
The one being the highest number and the none if there are no records in the view?

Yes, your assumption is correct...

Stanley

 

[stanlyn]

Just maybe... I'm beginning to get the picture... I've been trying to use remote views like tables and cursors by building a view of each sql table containing all fields, and being able to use the view for everything by treating it like a table using indexing, replaces, ordering, creating additional cursors and etc.

Stanley

 

[Chris Miller]

like in the case of finding something in the cursor and then needing to change its value and writing it back to the underlying table.

You don't fetch all data, then locate one record, change that and store it back with the change. You don't have the luxury of a USE command, SELECT * pulls all data, so you write your view query to only fetch the data you actually need. Which is Max(SessionId) of the records for the current SystemID.

So a query like

select Max(SessionId) as MaxSessionId from rv_session where SystemId = ?m.lcSystemId

And Mike already gave you the tip on how to use expressions like MAX() in a view definition.

And while it's only a subkey and not the main PK, it has to be unique within the records of the same SystemId, doesn't it? Then that's also the job of the database to create this number. Otherwise, you always risk double numbers in a multiuser system. In your case only when two users of the same SystemId work on data, but that's usually the case, isn't it?



Chriss

 

[stanlyn]

Chriss,

What is the difference with your use of ?m.lcSystemId and my use which is '<<lcSystemId>>' ?

Is one better than the other?

Thanks,
Stanley

 

[stanlyn]

	Select 'rv_Session'
	Append Blank
	Replace AppTitle With gcAppTitle
	Replace SystemId With gcSystemId
	Replace SessionId With gnSessionId
	Replace StartTime With Ttoc(gtStartTime)

	Tableupdate(.T., .T., 'rv_Session')
	Requery()

	lnConn = CursorGetProp("ConnectHandle", "rv_Session")

	SQLExec(lnConn, "SELECT @@IDENTITY AS Ident", "csrTemp")
	Public gnSessionPK
	gnSessionPK = csrTemp.Ident
	
	Use In Select('csrTemp')

Portions is from Mike's article at...

https://hexcentral.blogspot.com/201...,recently inserted row in a SQL Server table.

When running this code at full speed, it errors with a "connection is busy" when running the SQLExec(lnConn, "SELECT @@IDENTITY AS Ident", "csrTemp") line. If I step thru it line at a time, no issues.

Do we have to trap everything with a try catch routine? I thought one of the pluses of remote views is this sort of stuff was built in. From the looks of what I'm seeing, it isn't.

Thanks,
Stanley

 

[Chris Miller]

You're mixing several problems here, the thread was about determining max sessionid, wasn't it?
Now your fetching @@IDENTITY, which is the last PK, didn't you say sessionid is not the PK?

If it's a subkey you can't use INTEGER IDENTITY, you want to count per systemid, don't you? That makes multiple counters necessary. Or you don't care about gaps and use one counter and set systemid manually. It would be nice to know what the table definition is and what's automatic and whatnot, which indexes, keys, constriaints are defined, and such things.

In this case you only want submit to update your new record, don't you? Then don't Tableupdate with lAllRows = .T.

Your dealing with two separate systems, your app and the server. This is asynchronous, and a connection can be busy because of other users, too. You use a connection shared, not one per workarea.

In your case instead of @IDENTITY you'd rather SELECT SCOPE_IDENTITY(), see

https://blog.sqlauthority.com/2007/...nt-retrieve-last-inserted-identity-of-record/

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Chriss

 

[stanlyn]

Chriss,

You are correct in that I have mixed two entirely different issues here, however both boils down to reading and writing remote views, which is kind of why I put it here instead of starting a new thread... Sorry...

Above, the "connection is busy" error was traced to the requery() command. Disabling it made the error go away. Problem is, 20-30 line later in code I need access to the newly created record to add more data once the data is known. When originally created, we only had enough data to create and start a session. I also get and save the new records PK for later use. Once authenticated, I add user identity info to it and the logon form and its attached views are closed.

Now for the @@Identity question. In Mike's article I referenced above, he clearly indicated what was wrong with using SELECT SCOPE_IDENTITY() and the reasons why @@Identity is better, I've totally missed the point. Maybe Mike will chime in... Instead of at the end of the session (minutes or hours) looking up the session to write an ending time to it by using the systemid and such, I now store the session's pk in a public variable and use that when when closing the session using a quick lookup to the pk.

Also note that doing a query for only the systemid and sessionid to find the max is slightly faster than doing it the original way of pulling all down, create an index, set order desc, go top, get larges number way. Four seconds down to 3 seconds, 25% increase. I haven't tried it yet, but I'm going to try a remote view made especially for this max thing, and hopefully that will deliver sub second result.

You also asked for some schema. The table has a pk that the database manages hence the need to capture it with @@identy upon creation of the session record. The session id is calculated based on the highest number of a subset and unique by concatenating systemid and sessionid together. The systemid is per location and stored and retrieved from a setup table.

What I'm really struggling and fighting with is all the errors, locks, busy, revert, updates and all things with remote views. With remote views, you can do this, but not that... Same with pass-thru. I'll get there, someday, and quicker with help from the experts like you and others...

Thanks so much,
Stanley

Thanks,
Stanley

 

[Chris Miller]

Well, sqlauthority is really an authority in SQL Server questions. I quoted the essential sentence telling you SCOPE_IDENTITY is better. To add my understanding: You get the last identity value created within the session. And that's the one for your new record. Because you own the session, you can be sure i's not for another table, as the last thing you did was inserting the newly appended record.

Edit: I now read Mike's article, I still disagree, I'll check the behaviour Mike found to hinder the usage of SCOPE_IDENTITY(), but it would be bad, if that's really the case. I guess we differ in some setting that enables you to actually have seperate SQLEXEC calls working within the same scope and so getting the correct SCOPE_IDENTITY and not NULL. One thing I can imagine is automatic transactions will mean the SCOPE_IDENTITY is lost as the actual command sequence internally then is BEGIN TRANSACTION, INSERT-SQL, COMMIT, and then the SCOPE_IDENTITY is reset to NULL. So just the right connection settings will enable to use the better SCOPE_IDENTITY.

Thanks for telling a bit about the table, but an actual technical full description of it would help more to understand the PK, systemid and sessionid columns. I still don't think I have the bigger picture of wh things go wrong for you.

Another hint may help: Since identity fields are handled by SQL Server and readonly once they are generated, they should never go into the list of updatable fields. If you do that TANBLEUPDATE tries to generate inserts also inserting a value into the identity field, which of course always fails.



Chriss