SELECT in Stored Procedure
-
Hi All,
I am looking for some advice with using an (SQL2000) SELECT in a stored procedure.
I have developed a real time inventory scanning package using third party software, when the hand held device scans a barcode I call a stored a stored procedure to insert the barcode text and other data into a table. Before the barcode data can be inserted into the table I have to check that the barcode scanned has not been scanned to any other Sales Orders.
My problem that I need query 4 tables that are identical to see if the barcode exists, and return the Sales Order Number if it is in 1 of the 4 tables.
(The identical record will only be in 1 of the 4 tables)
Currently I am doing four separate SELECT statements in the stored proc, I’m sure this is not the most efficient way of doing this.
I know your asking why 4 identical tables? This is done for speed. The scanning devices scan about 250K barcodes per day. When the order is dispatched the scanned data is achieved.
-
Ian - one way of doing this would be:
create procedure chkBarCode
@barCode Int
as
declare @salesOrderNumber int
if exists(select salesOrderNumber from tblSales1 where barCode = @barCode)
begin
select @salesOrderNumber = salesOrderNumber from tblSales1 where barCode = @barCode
return @salesOrderNumber
end
else
if exists(select salesOrderNumber from tblSales2 where barCode = @barCode)
begin
select @salesOrderNumber = salesOrderNumber from tblSales2 where barCode = @barCode
return @salesOrderNumber
end
etc.....
**ASCII stupid question, get a stupid ANSI !!!** -
You could also create a view of the required fields from the 4 tables and use the sp to query from the view.
Good Luck,
Darrell
-
oh - yours' is a much better idea darrell!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for the quick replys
I have not used views before, but I will have a go.
Cheers Ian.
-
If you are partitioning the data into 4 tables for performance reasons, then I think this might be defeating the purpose.
Since your search is based on a unique value such as barcode, then having 1 table and 1 index on the field will be best. Your search only needs to access 1 index and 1 table. B-Tree indexes search performance does not degrade much based on number of records.
If you have 4 tables, then for each of your search, you need to search 4 indexes and 1 table, therefore incurring additional IO.
If you are splitting the data for archiving reasons, I would ask how searching 4 tables for dispatched records is faster than searching 1 table for dispatched records? I am assuming there is a column that indicates dispatched records and you have an index onn that field as well. Please correct me if i'm wrong
-
sounds to me like the decision to pull everything out into 4 tables was a mistake, since you need to query all 4 tables anyway. Is there a difference really between putting the data in a single table, with a clustered index on the barcode, and creating an indexed view that pulls the 4 tables into a single view with a clustered index? seems like its 6 of one/half dozen of the other, really;
I would reckon a view something like this would be the view to pull all the data together:
CREATE VIEW VW_SALES WITH SCHEMABINDING AS
SELECT * FROM tblSales1
UNION SELECT * FROM tblSales2
UNION SELECT * FROM tblSales3
UNION SELECT * FROM tblSales4
CREATE UNIQUE CLUSTERED INDEX ixBARCODE ON dbo.VW_SALES (barCode)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Rather than "SELECT *" I would think you'd want to limit this to only what you need, something like "SELECT BarCode..." since, if I'm not mistaken, "SELECT *" adds overhead. UNION queries seem to be rather slow in my experience the way it is...
-
Yup union will add a DISTINCT in the final query. union all doensn't. But I agree that splitting this data in 4 tables seems like a bad idea.
-
Try this:
SELECT dt.SalesOrderNumber
FROM (select
BarCode, SalesOrderNumber
from tblSales1
UNION ALL
select
BarCode, SalesOrderNumber
from tblSales2
) dt
where dt.barCode = @barCode
The UNION ALL does not weed out duplicate rows and is not as expensive but my not be the correct solution in all situations.
I like to do these kind of querys using a derived table (hence the dt alias).
The above code is thoroughly untested and shooting completely from the hip...
hth.. Mark
-
If you have a logic to partition the values with a check constraint then the partitioned view schema is going to work for you. SQL will not scall all four tables. If you can't set a check constraint then you will have to use a "normal" view or derived table!!
BTW: you can not create an indexed view on views that contain the UNION operator !!
* Noel -
Hi All,
Thank you for you valued input.
I did not explain why I have split the data into four tables.
Project Scope: The Company’s products are sold by weight and thus the stock is serialised (unique barcodes). There are two types of Orders, Sales Orders and Transfer Orders. I have written third party software that runs on RF Scanners which provides real time order picking. The scanner operator selects a released order from the scanner which sets up the allowed products, date ranges and ordered quantity that the scanner is allowed to pick.
Now when the operator scans a barcode that matches the above criteria the scanning software has to check that the barcode scanned has not been scanned to any other order. This is where I want to use SQL View to check for a duplicate in the scan buffer tables. I want to check all records in the Scan buffer tables and records less than 2 day old in the archive tables.
Speed on the scanning device is critical. It must allow or disallow the scanned barcode within 0.5 second to 1.0 second. Because of the huge volume of records, I spit the tables so SQL would only be looking at about 50,000 records as apposed to looking at one table with 10 million records. Please I am not an expert when it comes to db’s, so feel free to tell me that I have got this all wrong.
So heres is the method in my madness.
Table 1. – “Sales Orders Scan Buffer” (Index’s- Barcode)
This table stores the barcode, picking info (SO No, product, weight, operator and Date Time Stamp)
Table 2. – “Sales Orders Scan Buffer Archive” (Index’s- Barcode, Date Time Stamp)
When a Sales Order is dispatch and an invoice for the goods produced, barcodes scanned to the sales order are removed from the scan buffer Table 1 and placed in this archive Table 2.
Table 3. – “Transfer Orders Scan Buffer” (Index’s- Barcode)
This table stores the barcode, picking info (TO No, product, weight, operator and Date Time Stamp)
Table 4. – “Transfer Orders Scan Buffer Archive” (Index’s- Barcode, Date Time Stamp)
When a Transfer Order is dispatch and a transfer docket for the goods produced, barcodes scanned to the transfer order are removed from the scan buffer Table 3 and placed in this archive Table 4.
-
/*-----------------------------
select newid() as "BARCODE",a.* into part_1 from
master..sysobjects a cross join (select top 10 * from master..sysobjects) b;
-----------------------------*/
Table 'part_1'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'sysobjects'. Scan count 2, logical reads 26, physical reads 0, read-ahead reads 0.
(13120 row(s) affected)
/*-----------------------------
select newid() as "BARCODE",a.* into part_2 from
master..sysobjects a cross join (select top 10 * from master..sysobjects) b;
-----------------------------*/
Table 'part_2'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'sysobjects'. Scan count 2, logical reads 26, physical reads 0, read-ahead reads 0.
(13120 row(s) affected)
/*-----------------------------
select newid() as "BARCODE",a.* into part_3 from
master..sysobjects a cross join (select top 10 * from master..sysobjects) b;
-----------------------------*/
Table 'part_3'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'sysobjects'. Scan count 2, logical reads 26, physical reads 0, read-ahead reads 0.
(13120 row(s) affected)
/*-----------------------------
select newid() as "BARCODE",a.* into part_4 from
master..sysobjects a cross join (select top 10 * from master..sysobjects) b;
-----------------------------*/
Table 'part_4'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'sysobjects'. Scan count 2, logical reads 26, physical reads 0, read-ahead reads 0.
(13120 row(s) affected)
/*-----------------------------
create index ix_part_1#barcode on part_1(barcode)
-----------------------------*/
Table 'part_1'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
/*-----------------------------
create index ix_part_2#barcode on part_2(barcode)
-----------------------------*/
Table 'part_2'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
/*-----------------------------
create index ix_part_3#barcode on part_3(barcode)
-----------------------------*/
Table 'part_3'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
/*-----------------------------
create index ix_part_4#barcode on part_4(barcode)
-----------------------------*/
Table 'part_4'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
/*-----------------------------
select * into all_parts from
(select * from part_1 union all select * from part_2 union all select * from part_3 union all select * from part_4) t
-----------------------------*/
Table 'all_parts'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'part_4'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
Table 'part_3'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
Table 'part_2'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
Table 'part_1'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0.
(52480 row(s) affected)
/*-----------------------------
declare @barcode uniqueidentifier
set statistics io off
select top 1 @barcode = barcode from part_3
set statistics io on
select * from part_1 where barcode = @barcode
union all
select * from part_2 where barcode = @barcode
union all
select * from part_3 where barcode = @barcode
union all
select * from part_4 where barcode = @barcode
-----------------------------*/
BARCODE name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ------------------------------------------------------ ------- ----------- ---------------- ---- -------- ------- -------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ------
C806BE60-ACA9-451D-AF37-00110A66C29E sp_serveroption 1346103836 P 1 0 -1073741823 0 0 0 2000-08-06 01:31:08.717 0 0 0 P 0 4 0 2000-08-06 01:31:08.717 0 0 0 0 0 2 0
(1 row(s) affected)
Table 'part_4'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'part_3'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'part_2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'part_1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
/*-----------------------------
declare @barcode uniqueidentifier
set statistics io off
select top 1 @barcode = barcode from part_3
set statistics io on
select * from all_parts where barcode = @barcode
-----------------------------*/
BARCODE name id xtype uid info status base_schema_ver replinfo parent_obj crdate ftcatid schema_ver stats_schema_ver type userstat sysstat indexdel refdate version deltrig instrig updtrig seltrig category cache
------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ----- ------ ------ ----------- --------------- ----------- ----------- ------------------------------------------------------ ------- ----------- ---------------- ---- -------- ------- -------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ------
C806BE60-ACA9-451D-AF37-00110A66C29E sp_serveroption 1346103836 P 1 0 -1073741823 0 0 0 2000-08-06 01:31:08.717 0 0 0 P 0 4 0 2000-08-06 01:31:08.717 0 0 0 0 0 2 0
(1 row(s) affected)
Table 'all_parts'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
-
hi folks,
The post above gives an example why partitioning isn't a good idea, when you have a unique indexable column for your queries.
Partitioning = 2 + 3 + 2 + 2 reads. If you note, the 3 is on part_3 where we know the record is located. So 2 reads for each index, and 1 read to get to the row in thhe table.
Single table = 3 reads (2 reads for the index and 1 for the table).
Unless you are doing a FULL table scan everytime on your table (which is very bad for large tables), partitioning isn't going to help your queries.
-
Hi Khoo,
I gather from what your examples shows that spliting the data into 4 tables does not help the speed issue.
Should I put the data in a single table?
Should I use a view or stroed procedure to check if the barcode has already been used
or something else?
Regards Ian.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply