Quakedude's Dodgy SQL Questions #3

[QuakeDude]

Hey Guys,

Guess whos' back... back again..... (sorry)

Ok, carrying on from the last lot of "don't you know how to normalise a database" discussions I had with various people here, I have another question, hopefully this one is going to look a little more like real SQL and less like "Holy crap! Whats that supposed to be??!" Code..

Quick backgrounder, I have two tables as follows:

1 - tblRawImportData, contains one row per transaction of a monthly account
2 - tblSubmittedData, contains multiple rows of what you did with each transaction in the first table, ie. Table 1 shows that you pulled $100 from an ATM (for example), table 2 has 4 rows detailing the 4 items you bought with that $100, and how much each cost. The link between the tables is the TransactionID column.

So..

I'm trying to run this stored procedure, which should theoretically show me a summary of each statement in the raw data table, and is supposed to total up all the submitted rows in the submitted table and display them as a single total next to each row of the raw data summary.

Only problem is, the SubmittedTotal select-within-a-select is returning a consistent NULL for each row.

The query is as follows:



If'd I'd copped an error, I'd be happy, but since I don't, and its nearly 7pm, I'm struggling to see the tree from the forrest, if that makes any sense.

Can someone point out my obvious flaw in what I'm trying to achieve here?

[tin]

OK, cant think of a worse way to store data than MS-SQL , but anyway...

Are you getting any data from the inner most select?? That seems like the most obvious place it could break with all the WHEREs in there.

[QuakeDude]

Originally Posted by tin

OK, cant think of a worse way to store data than MS-SQL , but anyway...

Are you getting any data from the inner most select?? That seems like the most obvious place it could break with all the WHEREs in there.

Ok, started replying to this and realised that I had made a stupid error: The sub select should have read "WHERE AccountName=A.AccountName AND ImportName=A.ImportName", so the code now works.

Which brings me to a second question: This code, if run from within an ASP page (ie. no stored procedure) takes 2 minutes to run, and generally causes a script timeout. If I run it from within SQL Query Analyser, it takes 22 seconds, which is better.

Is the delay due to poor coding on my part, or simply because I'm running a sub select for each line? The tables currently contain approx 11,000 rows of data. I'm just wondering if there is a better way to code up the stored procedure to not have to cycle round for each row, as this is presumably going to take longer and longer to run as the number of rows increase every month..

[houseofzeus]

Originally Posted by QuakeDude

...

I don't know anything about MSSQL so I could be completely off base here but you could probably utilize a view and/or some indexing to speed up and/or eliminate the inner queries?

That's what I would be looking to do regardless of the DBMS in use anyway. Regardless take it with a grain of salt.

/edit: I suppose the ASP code you are using to call it could be useful as well, probably not to me but to others trying to help.

[QuakeDude]

I ended up running the SQL Index Wizard tool, and it came back and suggested I index on AccountName, not the TransactionID. After carrying out its recommendations, I'd down to a 3 second execution time

[trotsky]

Originally Posted by QuakeDude

Which brings me to a second question: This code, if run from within an ASP page (ie. no stored procedure) takes 2 minutes to run, and generally causes a script timeout. If I run it from within SQL Query Analyser, it takes 22 seconds, which is better.

Is the delay due to poor coding on my part, or simply because I'm running a sub select for each line? The tables currently contain approx 11,000 rows of data. I'm just wondering if there is a better way to code up the stored procedure to not have to cycle round for each row, as this is presumably going to take longer and longer to run as the number of rows increase every month..

The reason for this is that not only does it have to transfer the sql code from the asp app to the database, but it then has to create the query, compile it, do its query tree, query optimize... then run and return.

A stored proc is always best choice, fast; pre-compiled; query-tree done, and way more secure (no sql injection attacks).