Query for Next row based on an Index

[stanlyn]

Complicated query to do and ask/explain, at least for me, so here goes... Table= 'Page ' with an index named 'LibSort' build on field 'library_sort'.

I query the page table for a row (any row at this point) and what I want returned is the next row that would be next in-line if the table was ordered in LibSort order and only return a single row.

If the original query returned Deed book 200 page 54, then the query for the app's next button needs to return 'Deed book 200 page 55 if it exists, otherwise it needs to return the next higher number as shown in the LibSort index which could happen if it is at the end of book 200 and the next page is actually book 205 page 18. The point here is I need it to return the next page based on the LibSort index. I do not know what any of the keys would be, other than I've got an index based on the order that represents flipping through a book page by page.

Hope that explains,

Thanks, Stanley

 

[TamarGranor]

Edit: Just realized this is the SQL Server form, not VFP, so I'm editing my answer.

You can try something like this:

* LibSortExp here is the expression used for the LibSort tag.
* currentvalue is the value of the key expression for LibSort for the record 
* you're looking at now
SELECT TOP 1 * 
  FROM Page 
  WHERE LibSortExp > currentvalue 
  ORDER BY LibSortExp 
  INTO CURSOR csrResult

In addition, you should also take a look at the OFFSET and FETCH keywords, as they let you retrieve a group of records based on their position.

Tamar

Tamar

 

[stanlyn]

Hi Tamar,

-- LibSortExp here is the expression used for the LibSort tag.
-- currentvalue is the value of the key expression for LibSort for the record
-- you're looking at now

The field lib_sort was populated in VFP via a udf and copied as into the SQL tab;e's lib_sort field and then a SQL index was built on it.
With that being said wouldn't "currentvalue is the value of the key expression" be the actual value of the SQL's lib_sort's field? The lib_sort values looks like this... DEED~000000000000000000000088~~000000000117~0001 Does any of this change anything?

SELECT TOP 1 *
FROM DigitalAsset
WHERE LibSortExp > currentvalue

-- ORDER BY LibSortExp
No need as it only returns a single row, correct?

--INTO CURSOR csrResult
No need as I'm playing with it in SSMS, correct?

Thanks,
Stanley

 

[stanlyn]

Hi Tamar,

Based on what I've read about offset and fetch, it will not work as offset and fetch works on the returned recordset. My initial query only returns a single reoord therefore the record my needed "next" as in VFP record is not part of the set.

In vfp terms I would do it as...

select page
go 100 && just put me on a record, any record

set order to LibSort
skip 1

This is the behavior I'm trying to replicate all in SQL 2012

Thanks,
Stanley

 

[Olaf Doschke]

Assume you have a cursor selected with a library_sort field, which has the current value. Then do:

m.currentvalue = library_sort
SQLExec(conhandle, "SELECT TOP 1 * FROM Page WHERE library_sort > ?currentvalue ORDER BY library_sort","crsResult")

This query tells MSSQL to get the next row in library_sort order, as it asks for the top 1 row in library_sort order, which has a library_sort value bigger than the current one, that means the lowest library_sort just bigger than the current library_sort. Exactly what you need. MSSQL will notice it only needs one row, larger than one value you pass in, only the top 1 in the records having larger values. And it will get that row using the index on library_sort.

Commenting on your comments:

-- ORDER BY LibSortExp
No need as it only returns a single row, correct?

Wrong! Very wrong! Think about how TOP 1 decides what is the TOP 1 row. By the ORDER BY! Not ordering you can't use TOP. TOP needs an ORDER, queries with TOP don't work without an ORDER BY. And to get it very straight, without TOP 1 you get many records, you want only one, right? So you need TOP 1 and ORDER BY, no way around that.

Based on what I've read about offset and fetch, it will not work as offset and fetch works on the returned recordset.

What did you read? A mysql manual? C#?
OFFSET and FETCH are new T-SQL options of T-SQL queries. No need right now, if you only want to fetch one row.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

Assume you have a cursor selected with a library_sort field, which has the current value. Then do:

No, there is no vfp anything, all done inside SSMS and all is MsSql2012. The first query returns a result row while the next query (I'm asking about now) will return the row that is next in line based on SQL's LibSort index.

Thanks,
Stanley

 

[stanlyn]

Hi Olaf,

What did you read? A mysql manual?

https://www.mssqltips.com/sqlservertip/2362/overview-of-offset-and-fetch-feature-of-sql-server-2012/

Read the 2nd line in the "Problem" section.

and read the 1st line in

https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

I also solved this question with this code:

-- Flip to next page... 
use deedroom

declare @cLibSort varchar(100)
declare @cImagePath varchar(256)
declare @SystemId char(5)

set @SystemId = 'KY133'
set @cImagePath = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

SET @cLibSort = ( Select  da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId )

select @cLibSort as ViewValue

SELECT top 1 da.lib_sort, bf.bin_dataFS, bf.bin_type
  FROM DigitalAsset da 
  left join BinFile bf on da.pk = bf.digital_asset_pk 
  WHERE da.lib_sort > @cLibSort 
  ORDER BY da.lib_sort asc

Thanks for your's and Tamar's help,
Stanley

 

[Olaf Doschke]

Hi Stanlyn,

You mistake these texts, in a more precise way this should have said "...from the complete result set as it would be without OFFSET and FETCH".
You can tell on your own by looking at the sample queries, that this works in the single query itself. The examples given are queries working on the tables and OFFSET and FETCH are integrated in this and influence the final resultset. These clauses are not aftermath in the sense of your interpretation, but they are surely acting an the (virtually) sorted result set in memory, as it would be returned without OFFSET and FETCH. You have to understand though, that MSSQL as any SQL engine doesn't execute queries straight forward, an ORDER BY clause is not done on the complete table, when the SQL engine can optimize what you want. It knows the whole query, including the TOP 1 clause and or OFFSET and FETCH clauses. For example TOP 1 in conjunction with ORDER BY makes it the determination of a MIN value. In cunjunction with WHERE a MIN of a subset. You should have a sense of temporary result sets in memory which should be caused by one part of the query and be acted on by subqueries or clauses. The texts are meant with that thought in mind.

Well, it doesn't help you, as you only want to fetch one next row and the lib_sort value of that row helps more in your case, to use FETCH you'd first need to determine the OFFSET of that row, which would need a RowNumber Over Order By query, first.

In your solution code, you could substitute your initial query into the where clause to fetch the wanted row in the first place. My thought was, you repeat this and always have the current lib_sort as the input of the next page fetch. It's not a hard rule, but combining queries allows the engine to optimize the performance overall better than split up simpler queries. Unless queries get too complicated. For maintenance and readablility CTEs then can help more than really splitting into two or more queries.

SELECT top 1 da.lib_sort, bf.bin_dataFS, bf.bin_type
  FROM DigitalAsset da 
  left join BinFile bf on da.pk = bf.digital_asset_pk 
  WHERE da.lib_sort >  (Select da.lib_sort
  FROM DigitalAsset da2 
  where da2.image_path = @cImagePath 
  And da2.system_id = @SystemId)
  ORDER BY da.lib_sort asc

As you do [tt]select @cLibSort as ViewValue[/tt] you seem to want two table results returned, one table having single row and current lib_sort as only column, and the other table having next lib_sort, bin_dataFS, bin_type. That's ok.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

I've tried your version and it never finishes and I have to stop the query 1.4 hours... I made several variations and they none would finish. The last try was cancelled, see time indicator of embedded image. Any suggestions?

As you do select @cLibSort as ViewValue you seem to want two table results returned

Only temporally, just to make sure the value was what I was expecting. It would be removed in production.

Thanks,
Stanley

 

[Olaf Doschke]

How should this work at all with @cLibsort not set to anything? You only declared it, so it's an empty string. Using the form [tt]WHERE da.lib_sort > (subquery)[/tt] the subquery must return one single record. You also have stray code remnants in lines 22 and 24. Either make them comments or remove them, this doesn't make sense at all.

The code you have inits @SystemId and @cImagePath, not @cLibSort. That was done by your original first query you now integrated. You don't need @cLibSort now, you need the query as I gave it, to let the subquery return the one row it returns.

Is that perhaps not the case? Are there many rows for same image_path and system_id? Your current wrong version doesn't depeend on that, as you now can set @cLibSort to one unique value and be done with it, but it would mean your previous code set @cLibShort multiple times with one final value you used in your second query. Then this subquery doesn't work for combining them.

Last not least, could you just open your eyes for a moment, please? If you change...

(Select da.lib_sort
  FROM DigitalAsset da2 
  where da2.image_path = @cImagePath 
  And da2.system_id = @SystemId

...and instead now do...

(Select da.lib_sort
  FROM DigitalAsset da2 
  where da2.lib_sort = @cLibSort

...What row does this find in da2? da2.lib_sort = @cLibSort. This will be @cLibSort. In that case you don't need the subquery at all, you can replace it by @cLibSort only, you can simply set @cLibsort and only use your original second query:

-- Flip to next page... 
use deedroom

declare @cLibSort varchar(100)

set @cLibSort = '...something...'

[highlight #FCE94F]SELECT top 1 da.lib_sort, bf.bin_dataFS, bf.bin_type
  FROM DigitalAsset da 
  left join BinFile bf on da.pk = bf.digital_asset_pk 
  WHERE da.lib_sort > @cLibSort 
  ORDER BY da.lib_sort asc[/highlight]

And now look back to my suggestion (from the VFP side) doing:

m.currentvalue = [COLOR=#CC0000]crsResult.[/color]library_sort [COLOR=#4E9A06]&& from previous query[/color]
SQLExec(conhandle, "SELECT TOP 1 * FROM Page WHERE library_sort > ?currentvalue ORDER BY library_sort","crsResult")

You don't need a subquery at all, if your input parameter already is the lib_sort value. The result also has the lib_sort value needed as next input parameter. This is where we already were at quite some posts ago, and you didn't realize it. Finally wasn't that your plan overall anyway? You introduced the lib_sort column to now only need one reference value uniquely identifying a page AND sorting pages. Seems like you don't understand your own plan.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

How should this work at all with @cLibsort not set to anything? You only declared it, so it's an empty string. Using the form WHERE da.lib_sort > (subquery) the subquery must return one single record. You also have stray code remnants in lines 22 and 24. Either make them comments or remove them, this doesn't make sense at all.

1. Makes plenty of sense... Didn't you know that remnants do not have to be removed or commented out in SSMS? All you need to do is select the lines you want to run and press F5 or click the execute button and that saves me a lot of time...

As said before, this works correctly...

-- Flip to next page... 
use deedroom

declare @cLibSort varchar(100)
declare @cImagePath varchar(256)
declare @SystemId char(5)

set @SystemId = 'KY133'
set @cImagePath = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

SET @cLibSort = ( Select da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId )

SELECT top 1 da.lib_sort, bf.bin_dataFS, bf.bin_type
  FROM DigitalAsset da 
  left join BinFile bf on da.pk = bf.digital_asset_pk 
  WHERE da.lib_sort > @cLibSort 
  ORDER BY da.lib_sort asc

So if I have to add this same assignment select statement code to your script to get the value of cLibSort as in

SET @cLibSort = ( Select  da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId )

then what would make your script better than mine as they would both be equal in length.

I have to do a query to get this value, hence the select statement as it is not a known value that can be set literally. What I initially thought your script was doing was building it into the main select as a subquery. But I see now that you are wanting me to set it to 'something' without querying for it. The only 2 values that are known are @cImagePath and SystemId.

m.currentvalue = crsResult.library_sort && from previous query
SQLExec(conhandle, "SELECT TOP 1 * FROM Page WHERE library_sort > ?currentvalue ORDER BY library_sort","crsResult")

Again, as said earlier, this is not applicable here as there is nothing VFP going on. It is all done in SSMS... The value for "?currentvalue" must be the result of a select MSSQL query and not a VFP cursor.

Thanks,
Stanley

 

[Olaf Doschke]

OK, if you select what you execute, but post the whole script, how do I know, what you really executed???

And now the other route again:

SET @cLibSort = ( Select  da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId )

If that query to determine the @cLibSort value always is the same query and your real input is the two values of @cImagePath and @SystemId you set directly, then you can do this in the one query:

-- Flip to next page... 
use deedroom

declare @cImagePath varchar(256)
declare @SystemId char(5)

set @SystemId = 'KY133'
set @cImagePath = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

--select @cLibSort as ViewValue

SELECT top 1 da.lib_sort, bf.bin_dataFS, bf.bin_type
  FROM DigitalAsset da 
  left join BinFile bf on da.pk = bf.digital_asset_pk 
  WHERE da.lib_sort > ( Select  da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId ) 
  ORDER BY da.lib_sort asc

If this takes hours, then what does the single query give? Without setting SET @cLibSort?
You know, if you have something like SET @var = (query) and the query returns 1000 rows, the @var is finally set to the last result row, but you do 1000 assignments. That wouldn't work out good when used directly as subquery, but that would have a strong potential for improvement of this query.

So what does this return?

declare @cImagePath varchar(256)
declare @SystemId char(5)

set @SystemId = 'KY133'
set @cImagePath = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

Select  da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId

More than one row?

And last not least, if this query just is one example of getting some lib_sort, then finally you need two routines, one for getting some starting page, and that may be such a query, and then one pure fetch-next-page function, which only has a @cLibSort parameter and would do

SELECT top 1 da.lib_sort, bf.bin_dataFS, bf.bin_type
  FROM DigitalAsset da 
  left join BinFile bf on da.pk = bf.digital_asset_pk 
  WHERE da.lib_sort > @cLibSort 
  ORDER BY da.lib_sort asc

As that is where you end up, I think, you don't need to investigate further, how to integrate the two queries and why they are slow, when combined. You only need to go that route, if the final agenda is to be able to repeatedly fetch next page based on the two parameters @cImagePath and @SystemId. And then the solution would invovle finding a better query for getting at the concrete lib_sort value corresponding to image_path and system_id.

Bye, Olaf.

 

[stanlyn]

, if you have something like SET @var = (query) and the query returns 1000 rows, the @var is finally set to the last result row, but you do 1000 assignments.

In this particular query, it will always return 1 row because of the image path as shown in:
@cImagePath = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF' as this is unique...

The system_id was included because all queries may not include a unique field and may rely on multiple fields.

Sorry if I confused you regarding the execution of selected lines. I thought that you would know that if I ran the code as shown without the commented lines, I would be getting immediate unrelated errors, and therefore I had to be executing selected text...

When I run your code that combines it all into a single statement:

-- Flip to next page... 
use deedroom

declare @cImagePath varchar(256)
declare @SystemId char(5)

set @SystemId = 'KY133'
set @cImagePath = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

--select @cLibSort as ViewValue

SELECT top 1 da.lib_sort, bf.bin_dataFS, bf.bin_type, bin_size
  FROM DigitalAsset da 
  left join BinFile bf on da.pk = bf.digital_asset_pk 
  WHERE da.lib_sort > ( Select  da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId ) 
  ORDER BY da.lib_sort asc

It returns the correct results when it doesn't error, and it is about 1 second slower that the way I got it to work. The error happens randomly. I have never gotten that error using my 2 separate queries way. Any ideas?

When I run:

declare @cImagePath varchar(256)
declare @SystemId char(5)

set @SystemId = 'KY133'
set @cImagePath = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

Select  da.lib_sort
  FROM DigitalAsset da 
  where da.image_path = @cImagePath 
  And da.system_id = @SystemId

I get

Thanks, Stanley

 

[Olaf Doschke]

OK.

I had to be executing selected text

Fine, but why make me guess? If you select part of the query window, execute the selection, wouldn't it still be most easy for you to then CTRL+C and CTRL+P this very selection here and leave out any guesswork for us?Is that too much asked?

In regard to the dealocks, perhaps because I forgot to use a secondary alias da2 in the subquery again? It should not matter, this is some unusual self join, but queries involving the same table twice should not cause deadlocks, or a vast number of such queries wouldn't be possible or very slow. I don't have hands on your system and am not able to see what happens when querying data this and the other way. In theory the combined query would be faster, I don't see a resource sharing problem but the double use of DigitalAsset in this query alone, the resource problem should be related on any other queries going on at the server at the time you test. You should have a separate dev sql server.

You could use SQL Server Profiler to analyze the deadlock problem:

https://msdn.microsoft.com/en-us/library/ms188246.aspx

The deadlock would be with other queries running, if you test on a busy server your results will depend on what else is going on there anyway and you don't get a clear picture of which query variation is best. These DigitalAsset and Binfile tables seem to be very busy with inserts and updates of other sessions. In the end you might simply have a resource problem on your server and need to look into upgrading.

Bye, Olaf.

 

[stanlyn]

Is that too much asked?

No, it is not too much to ask, and I'll do it from now on. I can see why it may trip some folks up, but I really didn't expect that to trip you as I was really addressing you. So sorry, it won't happen again.

These DigitalAsset and Binfile tables seem to be very busy with inserts and updates of other sessions.

Yes they are extremely busy with inserts and updates. It is also a dev server, not production. The question arose as even though its extremely, one version worked flawlessly every time and the other version randomly had deadlock errors with the deadlock.

Thanks,
Stanley

 

[Olaf Doschke]

You don't stop puzzling me

Yes they are extremely busy with inserts and updates. It is also a dev server, not production.

Are you saying your dev server is busy?

Let me get back a bit:

I have to do a query to get this value, hence the select statement as it is not a known value that can be set literally.

I hought so. Initially I thought you had a libsort value at hand to begin with. You will have it, after the first run.

What I initially thought your script was doing was building it into the main select as a subquery.

It is.

But I see now that you are wanting me to set it to 'something' without querying for it. The only 2 values that are known are @cImagePath and SystemId.

What? Where is there a set statement. The subquery is just substituted for the value, there is no SET, there is a > comparison with it's result. The subquery result is goping straight into the comparison without first setting a variable.

The only 2 values that are known are @cImagePath and @SystemId.

Well, then you better integrate this. But in the end, I don't see why you feed in @cImagePath and @SystemId into a stored proc (I imagine you later turn tis into a parameterized stored proc), then finally don'T return the ImagePath and SystemId characterizing the next page. If you feed in the same initial values your nextpage function is not a next page function, fetch page n and get page n+1 function, finally a get page n+1 function, so you never get page n+2.

Your overall logic is flawn, if this isn't just for testing purposes, you showed the result value for libsort, so you could write it into a script. Again this would only fetch a certain next page. But you can store the libsort value somewhere, can't you? Memory variable, temp table, whatever, and then you can feed this into a next page or also previous page function. These page flip function onyl would need the last query and whatever you do to get an inital libsort value would be part of another subroutine.

If you instead intend to always only feed in a certain pair of values @cImagePath and @SystemId you better have one query. Find out what the deadlock problem is. I am quite convinced about more complex queries. It's not shorter code, that makes code run faster, its giving the possibility to the sql engine to better optimize your overal goal. Doing two queries compares to a case where someone posts a question, get's an answer, posts the next question, and then the expert says: Why didn't you tell in the first place? There is an overall much simpler solution! This specific case and the deadlocks "prove" me wrong, but in general that's true, sql server is quite good at optimizing.

Take a simple case, instead of [tt]SELET TOP 1 * FROM sometable ORDER BY somefield[/tt] you do [tt]SELET TOP 1 * FROM sometable ORDER BY somefield[/tt] and only use the first result row. Well, then sql server has to fetch the whole table in the wanted order. When you tell you just want the top 1 record that enables sql to look up the min value in an index and only fetch that single record. It will not sort all data in memory and then return the first row, it will only determine the first row. It will not just use the index on the field to sort faster, it uses it to look up the min value. And how would it do TOP N? Well, start with the top and then traverse the index in index order...

And in general splitting a complexer query into two smaller is only good for your better readability and maintainance, it's not good for MSSQL, unless you reach a point you clearly can optimize a partial query better or the partial queries are reusable and can be put together in other ways. In the end an applicatrion is having many different tasks and the goal never is to put all takss into one giant query capable to answering any request you may think of. Rule: No god class - same goes for SQL with queries. It's a matter of experience to know the granularity level of queries making sense. Too complex is as bad as too atomic.

Your query complexity is not that high, that you better explain it to the machine in two smaller tasks, I don't know what's hindering it to simply give a result in split seconds, as you say you have an index on lib_sort. Do you have no index on image_path and system_id? Anyway, as you can really store libsort somewhere - it's not that users have to enter it - you can just do the second query and have another routine finding a certain start page. Wouldn't that solve your problem?

Are we not actually finished with this problem, or is there still a problem left open? I can't help you with analyzing the deadlock from far away, I still don't know whether you want to be able to page forward with the two parameters also for the next page after the next page, but I do know you could solve that by working with the direct lib_sort value you know from the first call and then only need the simple secondary query. For that reason a split into two procedures would solve it, wouldn't it? You might even have a second third, fourth way to determine some starting point lib_sort value, but after that only need next/previous page code working with just the lib_sort column and index, right?

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

your dev server is busy?

Yes, importing 6 million records for testing...

You will have it, after the first run.

Yes

These page flip function onyl would need the last query and whatever you do to get an inital libsort value would be part of another subroutine.

Yes, correct. The initial query could be something like

select @var1=da_type,  @var2=book_num,  @var3=da_prefix,  @var4=da_num,  @var5=da_sub, @var6=system_id, @var7=vfp_key 
 from DigitalAsset where image_path = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

Note the "where image_path" clause was used to get a specific row from the table so I can test the "Flip Next and Previous" rows against a known row. Also note that I cannot use the image_path to get the next and previous records as all of the parts that makes up the image_path were trimmed so the path would be valid. Instead, I have a fixed char(104) field named lib_sort that is created with this script (still under construction, but working)

use deedroom

--104=lib_sort length -- needs changed to char instead of varchar

declare @datype char(40), @bknum char(24), @prefix char(24), @docnum char(12), @pagsub char(4)
declare @countykey char(14), @libsort char(104)

declare @var1 char(40), @var2 char(24), @var3 char(24), @var4 char(12), @var5 char(4), @var6 char(5), @var7 char(9)  

select @var1 = da_type,  @var2=book_num,  @var3=da_prefix,  @var4=da_num,  @var5=da_sub, @var6=system_id, @var7=vfp_key 
 from DigitalAsset where image_path = 'H:\Letcher\County Clerk\Deed\88\116-1.TIF'

set @bknum = (SELECT REPLACE(STR(@var2,24),' ','0') )
set @prefix = (SELECT REPLACE(STR(@var3,24),' ','0') )
set @docnum = (SELECT REPLACE(STR(@var4,12),' ','0') )
set @pagsub = (SELECT REPLACE(STR(@var5,4),' ','0') )
set @countykey =  @var6 +  @var7

set @libsort = ltrim(rtrim(@var1)) + '~' + @bknum + '~' + @prefix + '~' + @docnum + '~' + @pagsub

print @var3
print @libsort
print @countykey

update DigitalAsset set lib_sort = @libsort where system_id+vfp_key = @countykey

and returns

It will not just use the index on the field to sort faster, it uses it to look up the min value. And how would it do TOP N? Well, start with the top and then traverse the index in index order...

One thing I still do not understand is "how do I tell sqlserver that I want it to use a specific index?" Is there a way to enforce that like in vfp where we can set order?

Do you have no index on image_path and system_id?

I have one on system_id and none on image_path. As explained above the "where image_path" clause above was only used to get at a known row to get its lib_sort value for testing this "Flipping Next" routine.

I still don't know whether you want to be able to page forward with the two parameters also for the next page after the next page,

Well that's easy enough as when the "get next" routine runs, it will get and store its lib_sort value so the next time the "next" button is clicked, there will only be one select that runs instead of the two I had to use here to get a known starting position.

Concerning are we done, it would be helpfull if some light can be shown on how to enforce an index's usage like vfp's set order does.

I'll work some more on the deadlock issues,

Thanks,
Stanley

 

[Olaf Doschke]

Concerning are we done, it would be helpfull if some light can be shown on how to enforce an index's usage like vfp's set order does.

Well, no and yes. This works more like rushmore in VFP, the sql engine decides what it does, but it does not act randomly, if you do a where field=value, sql server looks up the value in the index it finds on the field. SQL Server indexes are unlike VFP indexes, they can contain more than one field and you don't use expressions to build indexes, so MSSQL can decide to use an index having all columns needed to lookup an expression easier than in VFP, the decision also is based on what fields you want in the result. If an index with all fields can be used, that even saves the time for a lookup in the data pages, unlike VFP indexes, which only contain their own expression and a record number to point to the DBF data. MSSQL also takes more into account than just the index. It also has statisticts. In the MSSQL only lets you decide indirectly. Like with vfp query optimization by rushmore. And rushmore in VFP works better than your own index usage can do in many cases, as rushmore can combine multiple results of index usage of multiple indexes into the so called bitmaps it then can combine logically with bit operations (AND/OR) and finally, after all that manyfold - say SEEKS - ae done, only fetch those rows, whereas manually using an index you can a) sort data and easily traverse it in that order with SKIP (as stated in the TOP N optimization MSSQL will also do that) and you SEEK and locate a certain records. Since SQL Server offers you SQL only and the only way to act on single records is to define server side curdors, which are to avoid as much as possible, you have to revert from your VFP thinking to set based thinking. You have to think SQL, not LOCATE/SEEK/ITERATIONS, they are not your business.

A way to tackle how things are done is inspecting the execution plan(s), there is an estimated plan when MSSQL parses the code, which includes the knowledge about indexes, then there is the real execution plan, what was executed, you can learn from both, what's missing in indexes, for example. Management Studio does a good job there, I think you find better third party tools for any MSSQL feeature, I would look for redgate offerings. But none of them will introduce record set based acting on data without first fetching the data, the only first level access to data is SELECT queries or BULK EXPORT.

In regard with the deadlocks, things to try are clauses you don't know from MSSQL, NOLOCK for example. It's not a universal cure, but could help in your deadlock case. In this case of importing 6 million rows while testing, you don't have to investigate much. Common, wait for the import, you can only really work on the loaded data. That's like saying it takes hours to get a pack of eggs from the supermarket, if you go there while they fill up and you have to wait for the eggs to be in their shelf. That's like measuring read speed of a drive while another process is writing GB of data to it. There are multiple CPU cores, but still only one IO channel.

In regard of the NOLOCK hint, be warned by this article:

https://blogs.msdn.microsoft.com/davidlean/2009/04/05/sql-server-nolock-hint-other-poor-ideas/,

If all that hinders fast reads is your current data import, then the locks later may not at all be necessary. On the topic of deadlocks in general:

https://www.brentozar.com/archive/2011/07/difficulty-deadlocks/,

also in general Brent Ozar is a guy to follow.

Bye, Olaf.