Using data from a row - laugh at new guy!

[derren]

Dunce's question ...

Is there a SQL equivalent of the following steps which I would take using foxpro:

If I wish to get a value from a row in a table and insert it into another table/manipulate it/update something etc. I would select the table and locate the record that I wished to look at (using a "locate for ... " command). If I find it, the record pointer is on that row, so I can now reference the values of the fields using simple dot notation such as

thisvar = mytable.myfield
replace othertable.otherfield with mytable.myfield

and so on. I know that if I refer to the first table it is still looking at the same record unless I take other steps to move. I can refer to any field within that row to perform calculations and so on.

A lot of the SQL syntax uses selects to pull the value of a field - such as:

set @newvar = (select field from table where criteria)

Now, every bone in my body is screaming that I do not want to run a select query each time I need to reference a field's value, for performance alone. Is there another way to do this without selects? Am I right to question this or am I simply applying incorrect logic to this new language?

I don't have any objections to using the selects, but I can't help but feel that at this early stage of development that I must learn these fundamentals correctly.

Thanks a lot Derren
[Mediocre talent - spread really thin]

 

[SQLSister]

Well I started to reply to you and got sidetracked doing some actual work and what do you know my computer locked up and I lost the response I almost had finished. Gonna be that kind of day.

SQL server is optimized to use set-based language like Select rather than procedural language. So using select in your statemetns is not a bad thing, but a good thing with some qualifiers.

First your tables need to be properly indexed for best performance. Second it is not efficient if you plan to use the information multiple times in the same procedure to continually select from the base table(s). If you are selcting only one value you can place it in a variable for use later or if you will have multiple records you can put them into a temp table or table varaible for later use. Then while you still will probably use some kind of select statement later to use the data, you will not be selecting from as many records thus improving performance.

Select statements in subqueries may not be the most efficient way to do business, but they are used to avoid using an even slower methodology like cursors or while loops. But doing a select and storing the results in variables, then running an update statement using the variables wouldn't be faster than putting the select directly in the update statement. So this methodology is generally reserved for when you want to use the data more than once or need to do some sort of complex processing on the data before you use it again.

In general, if you can put it all in one statement, that is often more efficient than creating a step by step procedure with multiple statements to do something (although that is usually easier for the programmer to write). I'm not saying you should never use procedural statements; they are in the language for a reason. Just that you should look at ways to do things in one statement as a first preference.

 

[sguslan]

It depends on the development tools. I can not comment on FoxPro as i have not done any similar stuff in more than 8 years now.

But for instance, if you were to use the new MS development platform, .net Visual Studio, then you declare a row set which executes a query one time by the SQL database and then it stores the returned field values locally in a disconnected fashion. You can then appy any programming logicto the row set, i.e. moving forward or looking for a particular record....
Some tools , like powerbuilder and .net VS will even allow you to store the entire rowset on local disk if you so chose...
So in all, it depends on the programming tools. Most MS development tools and others like Powerbuilder have the logic similar to FoxPro though may me more powerful...

With any tool you choose, the SQL database will execute a query one time and return all rows satisfying the query.

 

[derren]

Thanks SQLsister, that's brilliant advice, I feel I can now proceed using selects where necessary, knowing that I am doing it the right way.

Thanks also to sguslan for your response Derren
[Mediocre talent - spread really thin]