|
Came across this artcile[^] (ROW_NUMBER)
But this is sql specific, while great help I think - if I were to use Sybase then this is not relevant. Is there anything from ADO.NET 2/3 by now? Or do we still need to implement it ourselves?
Reading up on NHibernate[^] - even they have paging facility.
Thanks
dev
|
|
|
|
|
|
Good stuff! - I think this is paging facilitated by ADO.NET (although DataAdapter only, won't help with say IDbCommand+ExecuteReader situation)
<br />
int currentIndex = 0;<br />
int pageSize = 5;<br />
<br />
string orderSQL = "SELECT * FROM Orders ORDER BY OrderID";<br />
SqlDataAdapter adapter = new SqlDataAdapter(orderSQL, connection);<br />
<br />
DataSet dataSet = new DataSet();<br />
adapter.Fill(dataSet, currentIndex, pageSize, "Orders");<br />
Have gone back in time and been doing MFC for two years - when was thsi available in dotnet I don't remember seeing it?
Thanks!
dev
|
|
|
|
|
Also yes seems Sql specific - i.e. IDataAdapter of type "SqlDataAdapter"
Example pattern:
<br />
int currentIndex = 0;<br />
int pageSize = 5;<br />
<br />
DataSet oDataSet = new DataSet("MyData");<br />
DbProviderFactory oDbFactory = System.Data.Common.DbProviderFactories.GetFactory(strProvider);<br />
IDbConnection oConn = oDbFactory.CreateConnection(); oConn.ConnectionString = strConn;<br />
IDbCommand oSqlCmd = oDbProviderFactory.CreateCommand(); oSqlCmd.CommandText = strCmd; oSqlCmd.Connection = oConn; oSqlCmd.CommandTimeout = 10000;<br />
IDataParameter param = cmd.CreateParameter(); param.ParameterName = "@employeeId"; param.Value = 1120; param.Direction = ParameterDirection.Input; cmd.Parameters.Add(param);<br />
IDataAdapter oAdapter = oDbProviderFactory.CreateDataAdapter(); oAdapter.SelectCommand = oSqlCmd; oConn.Open(); oAdapter.Fill(oDataSet, currentIndex, pageSize, "MyData"); ATTN! paging is Sql specific, i.e. oAdapter of type "SqlDataAdapter"<br />
DataView oView = oDataSet.Tables[0].DefaultView; oView.Sort = "Column1, Column2 DESC"; <br />
dev
|
|
|
|
|
Data adapter is implemented for:
- System.Data.Odbc.OdbcDataAdapter
- System.Data.OleDb.OleDbDataAdapter
- System.Data.SqlClient.SqlDataAdapter
- System.Data.OracleClient.OracleDataAdapter
I don't recall seeing paging with ExecuteReader directly. Of course if you implement a stored procedure which returns a result set and take paging position as parameter, you can use that with ExecuteReader.
I think paging has been possible from .Net framework 1.1, see: Paging Through a Query Result[^].
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
to lazy to check each but I bet paging not available to all data adapters
dev
|
|
|
|
|
devvvy wrote: I bet paging not available to all data adapters
Why not. The operation is quite simple since the adapter reads all the records, but returns only those records you define by page settings. Rest of the records are thrown away.
Also this Fill version is defined in the base class (DataAdapter) so every inherited class have this functionality.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi.
I want to concat some row field in a string.
for example its my view
name id
---- ---
RRR 3
eee 1
ddd 3
i want to pass the id "3" to my function and get the "RRR ddd" as my result.
sepel
|
|
|
|
|
You don't say what's your problem exactly. Create a function where id is passed as parameter and varchar as return type. In the function you fetch and concatenate the strings.
Refer to CREATE FUNCTION (Transact-SQL)[^].
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Tank you.
Mika Wendelius wrote: In the function you fetch and concatenate the strings
My problem is That function.
How i should Fetch rows?
sepel
|
|
|
|
|
|
|
Hi All
I Wount to " Select 15 & 10 " from Pervaisve Version 8 DataBase
But the Pervasive give me Error For The Sql Statment .
How To Do This Sql Statment In Pervasive 8 ?
Thanks for Any body help me ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Thaer
|
|
|
|
|
I use the following code to ruturn value
SELECT Tbl_BuildingAggrement.*, Tbl_Building.*, Tbl_BuildCustomer.*, Tbl_Rent_Receipts.*, Tbl_Temple_Registration.*
FROM Tbl_BuildingAggrement INNER JOIN Tbl_Building ON Tbl_BuildingAggrement.BL_Building_Id = Tbl_Building.Build_Id
INNER JOIN Tbl_BuildCustomer ON Tbl_BuildingAggrement.BL_Cust_Id = Tbl_BuildCustomer.BuCust_Id
FULL OUTER JOIN Tbl_Rent_Receipts ON Tbl_BuildingAggrement.BL_Id = Tbl_Rent_Receipts.RntAggId
CROSS JOIN Tbl_Temple_Registration
WHERE Tbl_BuildingAggrement.BL_Building_Id =1 AND Tbl_BuildingAggrement.BL_AggDate between CONVERT(DATETIME, '22/Mar/2009', 102) and CONVERT(DATETIME, '22/Nov/2008', 102)
It display null value ,but i want to display Tbl_Temple_Registration details. that i used in this cross join.
But it didn't display values .What the Solution for this .If cross join return values .
|
|
|
|
|
I seriously think you need to use an inner join. Do some reading on join types.
If you split the selects do they both return results!
If query 1 return 3 records and query 2 return 5 records you will have 15 records in your result set.
You do not need to convert your dates to string to do the filtering, this will work
Tbl_BuildingAggrement.BL_AggDate between '22/Mar/2009' and '22/Nov/2008'
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
My question is when i use cross join it will return value or not?
All other tables other than cross join when i apply condition it will not return value .In cross join table i need to truen that values bcs it contain comapny details.I think u can understand my problem.
|
|
|
|
|
Hi all,,,
Im using Reporting services...
I ve taken Count(StudentId.value) to find out the Total number of Students for a particular year...
Now i need to SUM the Count(StudentId.Value) using expression...
Pls any one suggest some Solution...
|
|
|
|
|
I have inherited some code that runs like a pig (I think because of numerous udf calls).
I am not yet sufficiently skilled at T-SQL to figure out how to fix this, maybe someone can give me a couple of pointers?
Structure:
5 Tables:
PType ID Name
PCat ID PTypeID Name
Prod ID PCatID Name
Prop ID PTypeID Name
PropVal ID ProdID PropID Value
all ID's are Primary. All xxxID's are Foreign
In order to access PropVal Value data, the previous dev has used a udf to return the value.
This now means that heaps of sp's are littered with this function, causing what I believe are pretty serious
performance issues.
Example:
SELECT p.Name, Case When pt.Name LIKE 'IN%'
Then dbo.fGetPropVal(p.Name.'SHOT')
Else 0
End As 'SHOT',
Case When pt.Name LIKE 'EX%'
Then dbo.fGetPropVal(p.Name,'LEN')
Else 0
End As 'LEN',
...
FROM Prod p
JOIN PCat pc
ON p.PCatID = pc.ID,
JOIN PType pt
ON pc.PTypeID = pt.ID
...
(udf - like...)
SELECT Value
FROM PropVal pv
JOIN Prop pr
ON pv.PropID = pr.ID
JOIN Prod p
ON pv.ProdID = p.ID
JOIN PCat pc
ON p.PCatID = pc.ID
JOIN PType pt
ON pc.PTypeID = pt.ID
WHERE p.Name = @PName
AND pr.Name = @PropName
Is there much I can do with this?
Any advice or pointers in the right direction will be most appreciated.
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Disclaimer: The following codes may contain several typos etc. They are just provided to give some ideas 
Perhaps the easiest transformation would using a scalar instead of UDF. The following won't be the best version when considering performance, but it allows the optimizer to reorganize the query if possible:
SELECT p.Name,
Case When pt.Name LIKE 'IN%'
Then (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE p1.Name = p.Name
AND pr1.Name = 'SHOT')
Else 0
End As 'SHOT',
Case When pt.Name LIKE 'EX%'
Then (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE p1.Name = p.Name
AND pr1.Name = 'LEN')
Else 0
End As 'LEN',
...
FROM Prod p
JOIN PCat pc ON p.PCatID = pc.ID,
JOIN PType pt ON pc.PTypeID = pt.ID
...
The next version could be using joins. Those scalars can be dropped to inline views. However, without knowledge about the amount of rows, logic etc, it's hard to describe the correct transformation. It coud be something like:
SELECT p.Name,
Case When pt.Name LIKE 'IN%'
Then iv1.Value
Else 0
End As 'SHOT',
Case When pt.Name LIKE 'EX%'
Then iv2.Value
Else 0
End As 'LEN',
...
FROM Prod p
JOIN PCat pc ON p.PCatID = pc.ID,
JOIN PType pt ON pc.PTypeID = pt.ID
LEFT OUTER JOIN (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE pr1.Name = 'SHOT') iv1 ON iv1.Name = p.Name
LEFT OUTER JOIN (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE pr1.Name = 'LEN') iv2 ON iv2.Name = p.Name
...
Hope this helps you.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Make sure all the FKs have indexes (no code change)
Create a view with the UDF code without the where clause. You could then target your worst performing procs and join to the view instead of using the udf
Also I think in and like in the case statement may be a performance issue, try changing it to
when left(pt.name,2) = 'EX'
It may give you better results.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Also I think in and like in the case statement may be a performance issue, try changing it to
when left(pt.name,2) = 'EX'
It may give you better results.
Actually that could lead to weaker performance. When the LIKE is used as in the query was, it's possible for the database to use horizontal index seek.
If the column is wrapped inside a function, the database first has to evaluate the function for each row in the table and only after that it can eliminate rows not needed so this approach leads to full table scan for the table.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Yes, if you don't lead the condition with a wildcard (% or ?) then, using LIKE is much faster than =. So:
WHEN pt.name LIKE 'EX%'
will be much faster that:
WHEN left(pt.name, 2) = 'EX'
For one, the LEFT() function is being called on each row in the result set and then comparing the result against a string constant; whereas with the LIKE with the wildcard at the end will bypass the functio call and cause the engine to utilize the indexes (if any) to find matches on the string comparison. I'm not sure why, but from my experience whatever it's doing under the hood causes LIKE to generally out-perform =.
Similar to previous suggestions I'd move the value lookup out of a UDF either into the query itself, a CTE (Common Table Expression), an inline view, or a persistent view. They'll probably all give about the same performance, the choice would just depend on your needs. Since you mention it being used in many places, offhand it seems the persistent view would be the best option.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Lets consider a simple scenario. Students hand in their homework and an application is used to enter the date and time the homework is handed in and stored in the database. We need to keep track of students who hand in their work on time. So if a student always hands in homework, we can assign "Always" or some code to a column within the student record. If the student misses to hand work in on time a predefined number of times then status will become "Sometimes" or some other code.
The status can go from "Always" to "Never" but it can also go from "Never" to "Always".
We can start every student with "Always" and they will either stay at that level or drop lower.
How do you recommend I can fulfill such a requirement? Another table? A trigger?
|
|
|
|
|
Options as I see them
Store status on the student record and update every time the homework is touched (I would use a stored proc and NEVER a trigger)
Don't store status at all if it is only used for output, build the logic into a stored proc that services the request about status.
Create a view of the student table which calcs the status
There is no BEST way to do this, they are dependent on your requirements, style and preferences.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
One way to look at this problem is that you have 2 dates for each assignment.
You could create a function that counts the number of assignments given and determine the number of assignments that are late. Use a ration of late / total to determine a percentage of late assignments and when the ratio is too high, you could call them "sometimes" or "always" late.
Just a thought.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
