|
|
Posted by nano2k on 09/26/06 21:38
Hugo, Dan
Thanks for your patience with this topic.
I understand and agree to all your advices.
My struggle, at least for the moment, is to make the application as
much responsive as possible while strongly looking for data security.
Let's say that for the moment we don't care how much the SP needs to
process - we care only to let other users work while the SP is running.
The SP will be run only a few times a month, but there are 2-3 fixed
days of the month when the SP needs to perform - it's all about
accounting :) I only want to give other users the ability to work while
the SP runs.
I need uniqueidentifiers because my app is a three-tier client-server
app (client/server/db) that needs to generate unique IDs from client
side. The client app may generate several records from different tables
that will be sent all together in a single request to the server. The
records need to be already coupled (that is, foreign keys must be
already known before sending the request to the database) before
sending them to the server to process. Of course, there is an issue
here regarding indexing this column, as Dan mentioned. Anyway, I have
set a task to reindex the database every night, so I think this will
reduce the payback to using uniqueidentifiers - do you agree? Is there
any other action I should schedule?
Dan, my uniqueid column (named [objectid]) is the only key, and at
least at the moment, this column is set as primary key (ok? not ok?).
My protection to duplicate entries is handled by the client
application. 99% of the business logic is concentrated in client app.
The server side only minimally processes the request and deals with the
database (select/update/insert/delete). I fully understand that I have
to logically split my tables using other indexes.
Dan, you got right in the heart of my problem when you said: "In
general terms: you need to ensure that "old" invoices and "new"
invoices can be completely seperated". This is my goal at the moment.
Your inputs along with Hugo's inputs helped me to start my research on
the right path.
But today, I have discovered that the SP heavily uses CURSORS :((
This is the other big issue I have to deal with. Check out this piece
of code (one of 3 CURSORS defined in SP):
DECLARE inputs CURSOR FOR
SELECT i.accountid, il.doctypeid,
il.objectid,
(CASE WHEN ilb.objectid IS NOT NULL THEN ilb.objectid ELSE il.objectid
END),
il.itemid, ilb.batchid, d.updatestock * d.cogssign * (CASE WHEN
ilb.objectid IS NOT NULL THEN ilb.pdocquantity ELSE il.pdocquantity
END),
d.updatestock * d.cogssign * il.price * il.rate * (1 - il.discount /
100) * (1 - i.discount / 100),
il.currencyid, il.rate,
il.warehouseid, dbo.f_date_notime(il.stockdate)
FROM
inventory i
JOIN inventoryline il ON (il.inventoryid = i.objectid)
JOIN item it ON (it.objectid = il.itemid)
JOIN doctype d ON (il.doctypeid = d.objectid)
JOIN status st ON (st.objectid = i.statusid)
JOIN warehouse w ON (w.objectid = il.warehouseid)
LEFT JOIN inventorylinebatch ilb ON (ilb.inventorylineid = il.objectid)
WHERE
d.cogssign = 1
AND (st.final = 1 OR st.cancel = 1)
AND d.doctypekey NOT IN ('25','26')
AND il.stockdate >= dbo.f_date_notime(@last_date) + 1
AND it.stockcontrol = 1
AND ISNULL(w.nocost,0) = 0
ORDER BY il.itemid, il.stockdate, d.doctypekey, i.docnumber,
il.create_date, ilb.create_date
OPEN inputs
....follows fetching results in a loop
I want to change this in the following manner: to create an temporary
table and to insert the results of the above SELECT statement into this
temp table. After that, using a cursor, I intend to fetch the records
from the temp table. This way, the tables involved in the SELECT stm
above will be locked as short as possible. Do you think this is a good
approach?
Note: In the SELECT statement above: ALL tables involved have a primay
key defined as uniqueidentifier and named objectid. Also, ALL foreign
keys (FK) are indexed like this:
CREATE INDEX [FK_inventoryline_inventoryid] ON
[dbo].[inventoryline]([inventoryid]) ON [PRIMARY]
GO
Thanks again.
Navigation:
[Reply to this message]
|