Cursor versus Temporary Table

Hi All,
I am writing a stored procedure in which I need to read some records from a table which satisfy given condition, fetch the last read record and check its value.

e.g.
SELECT *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo.

I want to read the last record of each TestReqNo and check some values from that row. For this I was thiking of reading the above Select using a cursor and then using FETCH LAST to read the last row into some variable.

But this seems to be a round about way and also i have been reading that cursor will slow the execution. IS there any other better way. Should I use temp table instead, will that be more efficient ?

Please let me know your suggestions.
Thanks,
SnigdhaYou can reverse the order and just select the first record:

SELECT TOP 1 *
FROM TestRequestState
WHERE StateId = '11'
ORDER BY TestReqNo DESC|||Hi,
Thanks a lot for this solution, but there is still a little problem. The above query returns the last record of the last TestRequestNO. But I wanted to the last record of each TestRequestNO. For e.g.:

TestRequestNo TestRequestStateId
TR123 11
TR123 3
TR123 5
TR123 12

TR155 11
TR155 3
TR155 5

TR007 11
TR007 12
TR007 3

Now, out of this orderd set of TestRequestNos I want the last record of each of the TestRequestNo: TR123, TR155, TR007.

Thanks,
Snigdha

|||

So, something along these lines?

USE Northwind

SELECT t1.* FROM [order details] t1
WHERE t1.Quantity=
(SELECT MAX(Quantity) FROM [order details] t2
WHERE t1.orderid=t2.orderid)
ORDER BY t1.orderid

SELECT t1.* FROM [order details] t1 INNER JOIN
(SELECT orderid, MAX(Quantity) AS maxdate FROM [order details] GROUP BY orderid) t2
ON t1.orderid = t2.orderid
AND t1.Quantity = t2.maxdate
ORDER BY t1.orderid
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

|||Hey,
Thanks a loooot...
the first query worked perfectly the way I wanted it !!!! :)

There are minor things remaining which I think I should be able to handle.
Thanks a lot once again,
Snigdha

CTE Vs Temp Table in Yukon

Hi All,

I would like to know which gives better performance: CTE or Temporary Table?

Thanks,

Suresh

I wanted to add another information here.

When I replaced Temporary Tables with CTE in my query, it took more execution time. For ex., the query using Temp Table took 1 min 5 secs. The query using CTE took 4 mins. The no. of records hold by temp table in my query is apprx. 44000.

Why CTE is slower?

Suresh

|||

Check the execution plan, that is the only way you will find out. Clearly the CTE is using a different plan. You can force the execution plan to use a certain order using the FORCE command or option force_order at the end of the query.

Clarity Consulting (www.claritycon.com)

|||You cannot compare CTE and temporary table. They are different beasts. There are cases where you can break a complex query into simpler parts using temporary tables and get better performance. I am not sure how you used CTE in your query so it is hard to say. Note that CTEs also provide the capability to perform recursive queries in a declarative manner. And what are you measuring regarding the performance? Is it the temporary table creation vs query using CTE returning rows? If you are just measuring creation part then it doesn't include the time taken to send results to whatever client you are using. You need to elaborate on the actual problem. Best is to post a sample script that reproes the performance problem.

csv file to sql table

Hi...

I have .csv file(with headers) that i want to send its data to temporary table in sql.

Do i need to get the data as dataTable and than send it to sql table?

any way, i need help to do so...

thank you...

If you are using SQL 2005, you can use SSIS to load the data. If not see the example in http://www.codeproject.com/aspnet/ImportExportCSV.asp

|||

Hi may215,

may215:

I have .csv file(with headers) that i want to send its data to temporary table in sql.

Do i need to get the data as dataTable and than send it to sql table?

in the past i had a similar problem and solved it at this way you asked. Anyway. If you interested on it, here is a method in C# to read a structured textfile in a DataTable.

1DataTable table =null;2// set the delimeter3char _delim =';';45private void createTable()6{7// check if file exits8if (!File.Exists(_fileName))9{10// you have to change this to youre requirement11this._errorText =string.Format(CultureInfo.InvariantCulture,"Datei {0} ist nicht vorhanden, oder der Zugriff wurde Verweigert.",12this._fileName);13this._hasError =true;14throw new FileNotFoundException(_errorText);15}1617// create new DataTable18table =new DataTable();19table.Locale = CultureInfo.InvariantCulture;2021// String and StringArray initialize22string kontent =null;23string[] kontentArray =null;2425// create a FileStream26FileStream fs = File.OpenRead(_fileName);27// create a StreamReader and read the FileStream28using (StreamReader sr =new StreamReader(fs))29{30// read one line from the textfile , to create the columns in the DataTable31kontent = sr.ReadLine();32kontentArray = kontent.Split(_delim);33int i = 0;34// create the columns in the DataTable,35// the same number like the columns in the file36// beginn with 037foreach (string sin kontentArray)38{39table.Columns.Add(i.ToString(CultureInfo.InvariantCulture),typeof(string));40i++;41}4243// delete the old data from the buffer of the StreamReader44sr.DiscardBufferedData();45// set FileStream position at the line 2 from beginning46sr.BaseStream.Seek(1, SeekOrigin.Begin);4748// read the whole file in the DataTable.49while ((kontent = sr.ReadLine()) !=null)50{51kontentArray = kontent.Split(_delim);52table.Rows.Add(kontentArray);53}54}55// if FileSream not closed, close now56if (fs !=null)57{58fs.Close();59}60}

Hope could help a little.

|||

Hi...

thank you for your answer...

I have an odd problem, my csv file has headers, 10 in Hebrew, and 10 in English , the headers in hebrew are translate to "" sign...the headers in English are ok.

Why is that?

|||

may215:

Why is that?

Different cultures need different codepages and character sets.

may215:

I have an odd problem, my csv file has headers, 10 in Hebrew, and 10 in English , the headers in hebrew are translate to "" sign...the headers in English are ok.

I did not really understand the problem. As far as i understand your first post, you simple want to insert the content of the csv-file in a temporary table. So whats the matter with the headers? You can name the columns in your table however you want. So the header culture from the csv file is irrelevant. In the method i've postet prior, i simple read the header line into an array and count the contents from the array to generate the same count of columns. So when the hebrew content of a column is translated to " ", its also a content of an array. An empty one but a content, and so it is counted and a table column is generated.

If i understand your problem wrong, so please post back.