Solved

Help with Stored Procedure

Posted on 2004-09-13
8
261 Views
Last Modified: 2009-07-29
I have a stored procedure that contains two INSERT statements. When I perform the inserts manually, everything works fine, but when I put them in my stored procedure, they do not work. Basically, there aren't supposed to be any duplicates. The first insert inserts an edge into my graph, then the next insert is supposed to transitively close the graph, excluding the edge I've already inserted. These queries work fine by themeselves but inside the procedure, the initial edge does not get excluded.

Here is the Procedure:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO






ALTER       PROCEDURE TransClose
 @j INTEGER,
 @k INTEGER,
 @uid INTEGER,
 @is_cross_poll INTEGER
AS
BEGIN

begin tran
-- create new edge first
INSERT INTO tblGHGraph (cross_poll, fk_uid, init, term) VALUES (@is_cross_poll, @uid, @j, @k)
commit tran

begin tran
INSERT INTO tblGHGraph (cross_poll, fk_uid, init, term)
SELECT DISTINCT @is_cross_poll AS cross_poll, @uid AS fk_uid, G2.init, G3.term
  FROM tblGHGraph AS G1, tblGHGraph AS G2, tblGHGraph AS G3
WHERE -- define G1 as the new edge
  (G1.init IN (@j,@k) )--AND G1.term IN (4,2))
 --    AND (G2.term = G1.init AND G1.term = G3.init)
-- link G2 and G3 together thru G1
     AND G2.term IN (@j,@k)
     AND G3.init IN (@j,@k)
-- do not recreate any existing edges
     AND NOT EXISTS (SELECT *
         FROM tblGHGraph AS G4
         WHERE G4.init = G2.init
         AND G4.term = G3.term)
commit tran

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
Comment
Question by:dignified
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Author Comment

by:dignified
ID: 12049793
Failed to mention, this is also in MS SQL. Should have posted in the MS SQL section....
0
 
LVL 6

Expert Comment

by:gwalkeriq
ID: 12051402
Wow, transitive closure in a database, who'd of thunk it.

This will be horribly inefficient, as you add each vertex, you'll wind up with a cross join of 3 views of the same table only to eliminate a big chunk of rows via the "distinct" clause. You really should take a look at Warshall's algorithm (aka Floyd Warshall since Floyd is a refinement on Warshall). Plenty of this on the web. To be honest, I gave up looking at your database code once I figured out what you were attempting.

Warshall's algorithm is quite easy to implement, run's in N^3 (for the full transitive closure, not just 1 edge), and run's in-memory with a simple triple-nested loop using an adjacency matrix. If you must have the results in a database for some reason, use Warshall's, then dump the results to the database.

Warshall's is not the ultimate algorithm, but it is easy to understand and program. I guess you can run Warshall's on up to a couple of hundred nodes in under a second, I can't guess just slow your database solution would be (minutes? hours?)

Some simple Warshall's refs - source included. (There is many of these online). Sedgewick and similar books should explain this topic well too.

http://www.cs.rochester.edu/u/leblanc/csc173/graphs/tc.html
http://datastructures.itgo.com/graphs/transclosure.htm
0
 
LVL 6

Expert Comment

by:gwalkeriq
ID: 12051420
If you have to get the database to "do" the computation, build your edge list, dumping it into a table, then write a C program, etc. to run on the database server, slurp up the edge list, then dump the output from Warshall into a table. BTW, I don't intend to sound condescending, you would have a pretty smart guy to figure out how to do this using a database, it's just not the right way in this case.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 6

Expert Comment

by:rvooijs
ID: 12051837
Hi,

I have a couple of comments.

First, you seem to have one edge hard-coded in the procedure, which doesn't seem right
    WHERE -- define G1 as the new edge
       (G1.init IN (@j,@k) )--AND G1.term IN (4,2)) <----

Second, tho whole idea of transactions is to commit the whole procedure as one block.
The two insert statements are atomical by themselves and don't need to be enclosed by a transaction

I can't say for sure what's wrong with your code, but for starters I would take out all transactions.

You say you tested the insert queries by themselves. Are you sure there isn't some unique index
or foreign key constraint that's being violated ?

Robert
0
 

Author Comment

by:dignified
ID: 12057585
rvooijs - That hard coded edge is commented out....

Maybe I'll just have to have two procedures because I don't know whats up at the moment. As far as the complexity of the procedure. I don't plan on having to transitively close a huge dataset. The complexity for my purposes would be M^3 where M is a subset of the nodes N. M will prolly be no larger than 20. It is an interesting question to what would be the most efficient way to acheive the transitive closure. I'd like to see something more scientific to indicate if a SQL implementation is significantly slower than  taking the time to pipe the output to a C program and back. In either case I think what I have will be fine. If it isn't then I'll look into a platform optimized alternative.
0
 

Author Comment

by:dignified
ID: 12057863
Seems I found the problem... .this line....

SET ANSI_NULLS OFF


M$ did this for me, my initial nodes have NULL values... thanks M$!
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12740692
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question