Getting a pointer to a row from an Index

[stanlyn]

Hi,

Is there any way to get a pointer to a row from an index? Or is there a better way to do this. Let me explain...

Table: BOOKS with an INDEX on book_type + book_number + page-number. All these fields are char and padded to fill their fixed sizes.

Other fields include author, ocr_text and date.

Now we do a:
select author, ocr_text, date from books where author='jim' and ocr_text contains 'red oak tree' and between(date, '01/01/2013', '12/15/2013') order by author

... and yes I know some of the syntax is not correct, but anyway I get a result set back that matches the query. Now lets say our result set has:

Deed Book 232 Page 124
Deed Book 436 Page 444
Deed Book 501 Page 123
Mortgage Book 555 Page 741
Mortgage Book 474 Page 654

Note that these results matches what the query asked for... So far no problem as it gives us what we expect.

Our form has buttons "FIRST HIT", "PREV HIT", "NEXT HIT", and "LAST HIT" that traverses the matching query hits in our list above.
Our form also has 2 more buttons "PREV PAGE" and "NEXT PAGE" that simulates turning pages in the book WITH NO RESPECT to the query results. In other words, lets say we first arrive at "Deed Book 232 Page 124" and we press the "PREV PAGE" button, we should be seeing the page immediately to the left(previous) of it. Also note that whatever page we are looking at pressing the "PREV PAGE" will always take us to the previous page, while pressing the "NEXT PAGE" will always take us to the next page without respect of the query results. These next and previous pages we are navigating to may and MAY NOT be in our query results, as it navigates the library in library order without regard to the query results. The index above mentioned is the library order index.

Now, lets say that we want to look at the page IMMEDIATELY BEFORE "Deed Book 232 Page 124 SubPg 1" which is "Deed Book 232 Page 123 SubPg 1" assuming it exists, or it could be "Deed Book 232 Page 99 SubPg 1", if there are no pages between 124 and 99.

There is no way I can think of that allows me to find the prev row in library order from any given place as the only relationship it has is "they are beside each other" in the index and nothing else. I can't see making a primary/foreign key or any other column relationship work in a dynamic environment where new items are constantly coming in, other than reading this library index. So unless someone comes up with something, the only way I see this working is somehow reading the library index for a pointer to the prev and.or next rows.

And yes, I know that SQL Server doesn't have any concept of records. I come from a VFP background where this kind of stuff is easy...

Thanks, Stanley

 

[stanlyn]

More...

Also note that asked this question some time ago from a VFP CA perspective that describes pretty much the same thing. This question is now being asked from purely from a VS-C# perspective with no VFP stuff...

See:

http://www.tek-tips.com/viewthread.cfm?qid=1692860

Thanks, Stanley

 

[philhege]

One thing to keep in mind with SQL vs. VFP is that you can't traverse indexes in SQL. They're merely organizing/referring entities. And SQL is merely data. You don't have data objects the way you do in VFP, so your front end would have to manage those constructs.

Given there's no respect to the query results for your previous/next page functions, you'll simply have to construct a query based on the user's selection of the original query results. So you'll have to re-query your database for the next or previous page in the specific selected book or library item.

This assumes some sort of numbered result, so you can use the ROW_NUMBER() function to rank your query results.

-----------
With business clients like mine, you'd be better off herding cats.

 

[gmmastros]

Would another query help?

Now, lets say that we want to look at the page IMMEDIATELY BEFORE "Deed Book 232 Page 124 SubPg 1" which is "Deed Book 232 Page 123 SubPg 1" assuming it exists.

I assume you know the BookNumber and Page number that the user is currently viewing. So, perhaps you could execute a query like this:

Select  Top 1 *
From    Books
Where   BookNumber = (The currently viewed book number)
        And PageNumber < (The currently viewed page number)
Order BY PageNumber DESC

To get the next page...

Select  Top 1 *
From    Books
Where   BookNumber = (The currently viewed book number)
        And PageNumber > (The currently viewed page number)
Order BY PageNumber

Does this help?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stanlyn]

George,
> PageNumber < (The currently viewed page number)

Yes this will solve the problem if using the "top 1" will absolutely bring us the previous paqe. We don't really know what the previous page will be so we cant really look it up that way.
Will using the "top 1" return the absolute previous page or will it return any single page that is < current page.

We are gonna test this theory but if someone can concur that this is the behavior that would help a lot.

Thanks,
-Stanley

 

[stanlyn]

Select Top 5 *
From Page
Where county_id = 'KY131'
and page_type = 'deed' and Book_Num = '125'
And Page_Num < '250'
Order BY county_id, page_type, book_num, Page_Num DESC

Returns
25
249
248
247
246

 

[gmmastros]

I assume the 25 is causing you a problem. Can you tell me the data type of the Page_Num column?

Select Data_Type 
From   information_schema.columns 
where  table_name = 'Page' 
       and column_name = 'Page_num'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stanlyn]

Select Top 5 *
From Page
Where county_id = 'KY153'
and page_type = 'deed' and Book_Num = '123'
And Convert(int, Page_Num) < Convert(int, '250')
Order BY county_id, page_type, book_num, Page_Num DESC

Returns
99
98
97
96
95

Can you shed some light on why i'm getting these numbers back when i should get 249-245?

 

[stanlyn]

George,
Its CHAR12

 

[gmmastros]

Order BY county_id, page_type, book_num, [!]Convert(Int, [/!]Page_Num[!])[/!] DESC

In fact, you can remove the other order by's because you are filtering on those values (meaning they will always be the same).

Order BY [!]Convert(Int, [/!]Page_Num[!])[/!] DESC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

By the way....

If all of your page numbers are integer, you should change the data type for the column to int. Don't do this in production because you should thoroughly test your app before putting it in production. If all of your Book numbers are integers, then you should do the same with that column as well.

This, of course, should be considered a long-term goal. It will make your queries easier and more reliable. It will also use less storage. With your current structure (Char 12), you are using 12 bytes for every value. If you convert this to int, it will only take 4 bytes per value (saving 8 bytes per value). Multiply this by the number of rows in your table and you will get a good idea about the storage space you are wasting. If you are using the column in an index, the index would get smaller also.

Smaller DB's means more efficiency in performance also.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stanlyn]

Hi George,

Your order by clause works some of the time. When using:

Select Top 1 *
From Page
Where county_id = 'KY193'
and page_type = 'deed' and Book_Num = '87'
And cast(Page_Num as int) < cast('2' as int)
Order BY county_id, page_type, book_num, cast(Page_Num as int) DESC

it returns "Page 1" - which is correct

while this:

Select Top 1 *
From Page
Where county_id = 'KY193'
and page_type = 'deed' and Book_Num = '87'
And Page_Num < '2'
Order BY county_id, page_type, book_num, cast(Page_Num as int) DESC

returns "Page 199" - not correct

Ultimately I want to drop the page_type and book_number from the query so it can truely traverse the library index as my original question asks.

Say our table contains page types: bonds, deeds, marriages, and mortgages.

We know that the entire table is indexed using page_type + book_num + page_num, so if I'm looking at Deed book1 page1(which is the very first page in the Deeds) and I press the previous page button I'd expect it to go to the last page in the last(highest numbered) Bond book. With each iteration of pressing the "PregPage" button it should navigate backwards one page at a time crossing all book and page type boundaries.

Another way of looking at it is linearly thru the index.

Bond.................Deed......................Marriage.....................Mortgage.................. with each dot representing a book and page number. Pressing the PrevPage button navigates you to the immediate page before your current one (while moving toward the 1st page in the library, in this case Bond book1 page1. This new page you move to becomes the current page.

And yes George, I will be changing the page_num to int, however book_num must stay as char because some books are numbered as 125-B. I've also been playing with MAX(), DESC, and padding with no reliable results.

Thanks so much,
Stanley

 

[gmmastros]

The only difference I can see between these to queries is the filter on page_num

Select Top 1 *
From Page
Where county_id = 'KY193' 
and page_type = 'deed' and Book_Num = '87' 
And [!]cast(Page_Num as int)[!] < [!]cast('2' as int)[/!]
Order BY county_id, page_type, book_num, cast(Page_Num as int) DESC

[/code]
Select Top 1 *
From Page
Where county_id = 'KY193'
and page_type = 'deed' and Book_Num = '87'
And Page_Num < '2'
Order BY county_id, page_type, book_num, cast(Page_Num as int) DESC
[/code]

Here's the thing... Page_Num is a string so when you say, page_num < '2' it will return rows where page num is "alphabetically" less than 2.

Ex:

Declare @Temp Table(Fruit VarChar(20))

Insert Into @Temp Values('Apple')
Insert Into @Temp Values('Banana')
Insert Into @Temp Values('Strawberry')

Select * From @Temp Where Fruit < 'Grape'

The same thing is happening in your query. You are treating Page_Num as a number, but sql server is treating it like the string it actually is. By converting (or casting) to int within your queries, your queries should work better.

The problem is... there is a cost associated with converting to int. The price is paid in performance. You probably won't notice much of a difference because of your other where clause conditions but the penalty is there. This is why I suggested that you change the data type to int.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stanlyn]

George,

> And cast(Page_Num as int)[!] < [!]cast('2' as int)
is generating "incorrect syntax near '!' in sql 2012

What is the [!] for?

I completely get the "alphabetically" less than 2 part stuff (sorting char and numeric data). As I said earlier I will be changing the page_num to int after testing for char data in that field such as '255A'.

Any thoughts on the bigger issue I face with the navigation of the library as a whole?

I'm thinking of maybe adding a new library_pointer field that is numeric with 6 decimal places. The decimal places are for new rows after the initial population. I'd then need a proc that would populate it with whole numbers starting from 1.000000 for the very first row and increment by 1.000000 until the of table. This proc will need to be run initially and then on a schedule as part of a maintenance plan and would reset all rows to whole numbers, otherwise the system would not return correct results whenever more than 9999999 new records gets inserted between any 2 side-by-side numbers such as (4to5 or 10to11) .

Now for the assignment of the new value upon an insert. In real time, we would need to do a lookup using the new inserted rows page type, book and page numbers to find the row that is page_type + book_num + (page_num - 1), then assign the new inserted row's library_pointer field the value of the looked up row's library_pointer field + .000001

This is in essence creating an external index that we can hook into.

George, I'd like to hear your thoughts on this or a better way. Also, do you know if it is possible to hook into the sql index for pointer info, as the method I describe above is really doing externally what sql server is doing with it internally.


Thanks, Stanley

 

[gk53]

If you have page_num as '255A' tha tsql will not work... It was for numeric page_num in char field...
About addin fields I would preffer create another table with columns
page_type, book_num, page_num (char), pageNum int (numeric part), pageChar char (char part of page_num)
with index page_type, book_num, pageNum, pageChar
ones created fill with data from your original table and when original table get updates update that table too
in this case you can use that table as index
If you call that table Page_ind

declare @pagenum varchar(10), @pageint int, @pageVar varchar(5)

set @pagenum='256a'
--find int part of page_num
Select @pageint =  SubString(@pagenum,0,PATINDEX('%[^0-9]%',@pagenum))
-- find char part
select @pageVar = replace(@pageNum, cast(@pageint as varchar(5)),'')

Select Top 1 p.*
From Page p
join Page_ind i
on p.page_type=i.page_type
and p.book_num=i.book_num
and p.page_num =i.page_num 
Where p.county_id = 'KY193' 
and i.page_type = 'deed' and i.Book_Num = '87' 
And i.pageNum < @pageint and  i.pageChar < @pageVar
Order BY county_id, page_type, book_num, i.pageNum DESC, i.pageChar Desc

 

[gmmastros]

What is the [ignore][!][/ignore] for?

It's for this website. Specificially, it's TGML tags that should have been closed but wasn't. It basically makes the font bold and red. I use it a lot to highlight different parts of code.

Select Top 1 *
From Page
Where county_id = 'KY193' 
and page_type = 'deed' and Book_Num = '87' 
And [!]cast(Page_Num as int)[/!] < [!]cast('2' as int)[/!]
Order BY county_id, page_type, book_num, cast(Page_Num as int) DESC

I'll give the other stuff a few minutes to settle in my mind before offering a suggestion. I can tell you right away that I am not in favor of a numeric pointer that accomodates adding numbers in between.

How many rows are in your table?

What is the proper ordering of the pages as it is now (with your existing structure)?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stanlyn]

> I can tell you right away that I am not in favor of a numeric pointer that accomodates adding numbers in between.
George, it looks as if this is the best way to achieve true navigating the library one page at a time while leaving page type, book and page numbers out of the query.

> How many rows are in your table?
Greater than 10 million


> In real time, we would need to do a lookup using the new inserted rows page type, book and page numbers to find the row that is page_type + book_num + (page_num - 1), then assign the new inserted row's library_pointer field the value of the looked up row's library_pointer field + .000001

This had to be changed... Instead of adding .000001, I have to get the highest row (example 200.000000) and the lowest (199.000000) based on the currently viewed page. Whhen inserting, I add the 2 together (399) and divide by 2 (399/2=199.500000) which is in the center of the high and low. I insert new record in the middle allowing places for other new records.

Now, the big ? is how many inserts can be made using this centering method?

Thanks, Stanley

 

[gmmastros]

Now, the big ? is how many inserts can be made using this centering method?

I disagree. The real question is... how few inserts can I do before something breaks?

Imagine for a moment that you only had 2 digits (for talking purposes). Also imagine you currently have...

1.00 = AAA
2.00 = BBB

Now suppose you wanted to insert the following data. BAA, BAB, BAC, BAD, BAE, BAF, BAG

Inserting the first one, BAA, it would get numbered 1.5, so you would have...

1.00 = AAA
1.50 = BAA
2.00 = BBB

The next insert would be:
1.00 = AAA
1.50 = BAA
1.75 = BAB
2.00 = BBB

The next insert would be:
1.00 = AAA
1.50 = BAA
1.75 = BAB
1.88 = BAC
2.00 = BBB

The next insert would be:
1.00 = AAA
1.50 = BAA
1.75 = BAB
1.88 = BAC
1.94 = BAD
1.97 = BAE
1.99 = BAF
2.00 = BBB

The next insert for BAG would fail because there isn't any more room. So, with just 2 digits, we have run out of room with just 6 inserts. If you accommodate 6 digits, you will have more room for more inserts before something fails. My point is that you have to plan for the worst and then hope for the best.

Looks like your combination of CountyId, page_type, book_num, and page_num is really your primary key. More specifically, the combination of these four columns uniquely identifies a row.

It also appears (to me) as though the proper ordering for all the documents is CountyId, page_type, book_num and page_num. If you did a simple select, like this....

[!]Don't actually run this[/!]
[tt]
Select CountyId, page_type, book_num, page_num
From Page
Order By CountyId, page_type, book_num, page_num
[/tt]

This would return all the columns, but not in the correct order because you are storing your data in strings but hoping to order it as a number.

You said earlier that you have book numbers like this: 125-B. I assume that the proper ordering of books would be: 124, 125, 125-A, 125-B, 126. Is this correct?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gk53]

hi said '255A'...

 

[gmmastros]

In the post dated 17 Dec 13 15:22, he also said, "And yes George, I will be changing the page_num to int, however book_num must stay as char because some books are numbered as [!]125-B[/!]. I've also been playing with MAX(), DESC, and padding with no reliable results."



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom