HUGE number and SQL functions 2

[trix13]

Okay I am creating a zip code search page for our site. I have a function and a procedure in my SQL Server database that work just fine. However as luck would have it I've been instructed to make this process work through a dll I create in VB 6 instead of relying on our SQL server. Okay. To convert the work SQL is doing, it appears it's going to take 5 functions in VB. My first and biggest problem is that my formula requires the use of a HUGE number, over 8 billion, to do the math so to speak. I can't find anything in VB that will hold a number of that size without an overflow error. I've looked up every type of numeric I can use, I've cast the number, I know the limits and all. Does this mean there is nothing in VB that will allow me to use this formula? I mean do I have to re-configure the whole process of finding the zip codes with another formula because VB can't do what SQL does? I'm more inclined to think I don't know what I'm doing in VB. lol! Any suggestions please?

 

[johnwm]

trix13
Why don't you give the man a star? Just click on the 'Thank gmmastros
for this valuable post!' link at the bottom left of his last posting. It shows your appreciation, and marks this thread as being special so others can find it easily.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[trix13]

Thanks! Great idea. I'm still a bit new here. There's a star by my name on a reply to another post and I had no idea what it was! Detail oriented AND unobservant? Is that possible?

-trix
"Wiggle your big toe.

 

[BobRodes]

<Detail oriented AND unobservant? Is that possible?
If I'm any indication.

George, nice piece of work, worth another star from me. One question: regarding "So, you will have to create one ugly looking database call that looks like...[etc]", why can't you modify your original code to put the zip codes in a temp table, and then just check the table in the where clause? Wouldn't that avoid the messy IN list?

Bob

 

[gmmastros]

Bob,

When dealing with a database, using IN is faster than creating a temp table. Let me illustrate by example.

Open Query Anaylyzer
Add this code.

Select *
From   FavoriteTable
Where  PrimaryKeyField In(Val1,Val2,Val3)

Press CTRL-K (to view the execution plan)
Press F5

At the bottom of the window, there will be an execution plan tab. Click it. You'll see that SQL Server does a Clustered Index Seek to return the data.

Now, open a new window.
Put in this code.

Create Table #Temp(Id integer)
Insert Into #Temp Values(val1)
Insert Into #Temp Values(val2)
Insert Into #Temp Values(val3)

Select * 
From   FavoriteTable 
       Inner Join #temp 
          On FavoriteTable.PrimaryKeyField = #Temp.Id

Drop Table #Temp

Again, press CTRL-K
Press F5

You'll notice that the same data is returned. This time, looking at the execution plan, you'll notice that there are 3 inserts, and a select. Take a close look at the select. Notice the table scan, and it's eating up the highest percentage (meaning, it takes that 5 of time to complete).

When dealing with SQL Server, you want your procedures to be as quick as possible so that your app can scale to many users.

When writing queries, you should look at the execution plan to make sure it is efficient (especially for slow queries).

Generally you'll see 4 types of table accesses in the execution plan. From slowest to fastest...
1. Table Scan (the absolute worst)
2. Index scan
3. Index Seek
4. Clustered Index Seek (the best)



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Thanks George. One more question. If you did this:

Select * 
From   FavoriteTable 
       where PrimaryKeyField in (select id from #temp)

would that still do a table scan?

 

[gmmastros]

Yes. You still get a table scan.

Let me clarify a little.

The temp table is being 'table scanned' not the real table. So, if the temp table is relatively small, then no harm done. It's only after the table gets large are there issues with performance.

However, by using the IN operator (when dealing with a hard coded list), there is no table scan, only an 'index seek' or 'clustered index seek'. This really only applies to hard coded lists of items, so it shouldn't be taken too seriously.

I did a little test.

Using a table with 391,777 records.
I selected 1,805 Primary Keys in to a temp table

When retrieving the records (Select *)...

Using the IN Method: 76 Milliseconds
Using the IN(Select Field From #Temp): 250 Milliseconds

Make Sense? Hope it helps (at least to put it in to perspective).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

Hi again! Got put on other priorities for a few days. Back to my zips now. Hey George the db in:

Call db.Execute(cSQL)

VB is complaining about "db". Not being familiar - did I forget to declare something? What does this represent may I ask? Thanks!

-trix
"Wiggle your big toe.

 

[gmmastros]

I assume your VB app would communicate with the database through ADO. The DB in this case, would be your ADODB.Connection object.

I didn't want to 'dirty' the code with showing how to create an ADODB.Connection object.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

Oh okay you know that's what I figured I just wanted to double check. I'm working on my dll now. Can't wait to see it in action! I'll let you know how it goes...

-trix
"Wiggle your big toe.

 

[gmmastros]

thanks I'm VERY curious to know the performance difference between VB as SQL Server (in this instance).


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Thanks George, that clarifies for me.

B