Im not a db guru so wondering if any of you guys out there had a technique for adding a dynamic row id counter field that didnt use Count() or an Aggregate function?
Printable View
Im not a db guru so wondering if any of you guys out there had a technique for adding a dynamic row id counter field that didnt use Count() or an Aggregate function?
if you're using at least SQL Server 2010, you can use Row_Number() ... another technique I've used that's pretty universal is to dump the data into a temp table that has an identity/autoincrement field on it then return the data from there.
-tg
Thanks! We are using 2012 So far I got a non-impacting solution by using a variable counter in the return method in the model class. Its "ok" but not as elegant as could be I think.
Looks like row number may be more impactful on resources than using a counter variable in c# I assume?
I've found row_number to be quick and pretty light on resources. i've used it to sort out and count millions of rows in SQL with minimal impact. if there is any overhead or impact, it's so minimal it's negligible. No more than a sort.
-tg
Its a HA website so performance is always being scrutinized. Its in a hierarchical master / detail grid for invoicing. The ID is for the line item numbers in the invoice detail which are for display purposes / easier reading.
Its complicated by the kendo grid we are using
Code:public static List<InvoiceDetail> GetDetailByInvoiceID(DatabaseController databaseController, long invoiceID, long firmID)
{
long i = 0;
List<InvoiceDetail> invoiceDetails = new List<InvoiceDetail>();
string sql = "SELECT ";
sql += "[InvoiceDetailID], [InvoiceID], [FirmID], [CaseID], [CreatedDate], [CreatedBy], [UpdatedDate], [UpdatedBy], [Deleted], ";
sql += "[Location], [InvoiceDetailDate], [DetailAmount] ";
sql += "FROM [InvoiceDetail] ";
sql += "WHERE [InvoiceID] = @invoiceID AND [FirmID] = @firmID AND [Deleted] = 0 ";
sql += "ORDER BY [InvoiceDetailID] ASC;";
SqlCommand command = new SqlCommand(sql, databaseController.connection);
command.Parameters.Add("@invoiceID", SqlDbType.BigInt).Value = invoiceID;
command.Parameters.Add("@firmID", SqlDbType.BigInt).Value = firmID;
command.Prepare();
SqlDataReader reader = databaseController.query(command);
if (reader != null)
{
while (reader.Read())
{
InvoiceDetail invoiceDetail = new InvoiceDetail();
invoiceDetail = InvoiceDetail.initWithReader(reader);
invoiceDetail.RowNumber = ++i;
invoiceDetails.Add(invoiceDetail);
}
reader.Close();
}
return invoiceDetails;
}
well I don't know the Kendo Grid.
this is what I use for the DGV, it adds a Linenumber
see if you can't adapt it to the Kendo Grid
HTHCode:Public Class Form1
Private mRandomClass As New Random()
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim dtb As New System.Data.DataTable
dtb.Columns.Add("Column1", GetType(Integer))
dtb.Columns.Add("Column2", GetType(Double))
dtb.Columns.Add("Column3", GetType(Date))
dtb.Columns.Add("Column4", GetType(Decimal))
dtb.Columns.Add("Column5", GetType(Integer))
dtb.Columns.Add("Column6", GetType(Integer))
For i As Integer = 0 To 10
Dim Zahl As Double = mRandomClass.Next(100, 10000000)
Dim Datum As Date = Date.Now.AddSeconds(Convert.ToDouble(mRandomClass.Next(1, 1000000)))
Dim Geld As Decimal = mRandomClass.Next(1, 100000000)
dtb.LoadDataRow(New Object() {1 + i, _
Zahl.ToString("#,##0.00"), _
Datum.ToString("dd.MM.yyyy HH:mm:ss"), _
Geld.ToString("#,##0.00"), _
mRandomClass.Next, _
mRandomClass.Next}, _
True)
Next
Dim dvw As DataView = dtb.DefaultView
dvw.Sort = "Column3 ASC"
Dim dtbSorted As DataTable = dvw.ToTable()
DataGridView1.DataSource = dtbSorted
Me.DataGridView1.Columns(4).DefaultCellStyle.Format = "c"
End Sub
Private Sub DataGridView1_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting
RownumberToHeader(CType(sender, DataGridView), e, Color.Brown)
End Sub
Public Sub RownumberToHeader(ByVal dgv As DataGridView, _
ByVal e As DataGridViewCellPaintingEventArgs, _
Optional ByVal nColor As Object = Nothing, _
Optional ByVal lastRowOnly As Boolean = False, _
Optional ByVal selRowOnly As Boolean = False)
Dim d As New Date
Dim w As Boolean, numColor As Color, dFont As Font
With dgv
If .RowCount < 2 Then Exit Sub
Dim font As Font = .RowHeadersDefaultCellStyle.Font
dFont = New Font(font.FontFamily, font.Size, FontStyle.Bold)
Dim rc As String = Format(.RowCount, " #,##0")
Dim maxSize As SizeF = New SizeF(TextRenderer.MeasureText(rc, dFont).Width, _
TextRenderer.MeasureText(rc, dFont).Height)
.RowHeadersWidth = CInt(maxSize.Width + 25)
numColor = .RowHeadersDefaultCellStyle.ForeColor
If nColor IsNot Nothing Then numColor = CType(nColor, Color)
Dim k As Short = CShort(IIf(.AllowUserToAddRows = True, 2, 1))
Dim ri As Integer = 0
w = (e.ColumnIndex = -1 And e.RowIndex >= ri)
If lastRowOnly Then
ri = .RowCount - k
w = (e.ColumnIndex = -1 And e.RowIndex >= ri)
ElseIf selRowOnly And dgv.SelectedRows.Count > 0 Then
ri = .SelectedRows(0).Index
w = (e.ColumnIndex = -1 And e.RowIndex = ri)
End If
End With
If w Then
If dgv.Rows(e.RowIndex).IsNewRow = False Then
With e
Dim rowStr As String = Format(.RowIndex + 1, " #,##0")
Dim StringSize As SizeF = New SizeF( _
TextRenderer.MeasureText(rowStr, dFont).Width, _
TextRenderer.MeasureText(rowStr, dFont).Height)
.PaintBackground(.ClipBounds, False)
.PaintContent(.ClipBounds)
Using brush As New SolidBrush(numColor)
.Graphics.DrawString((rowStr).ToString, dFont, brush, _
.CellBounds.Right - StringSize.Width, _
.CellBounds.Top + (.CellBounds.Height - StringSize.Height) / 2)
End Using
.Handled = True
End With
End If
End If
End Sub
I've used Row_Number() Partition BY () on HA systems for years now. It was used on on-line reservations systems, financial systems with out a performance impact that caused issues
Depends on the guts of the server vs the client(s) so it's pretty much impossible to say. Neither is free but the Row_Number function is pretty light weight (as TG says, it basically resolves as a sort) and will be resolved on the DB server which is likely to have plenty of clout. Putting your logic in the client effectively distributes the processing, which is good, but does so to machines that are likely to be less gutsy than a server. Really you have to try out both and see what you get.Quote:
Looks like row number may be more impactful on resources than using a counter variable in c# I assume?
If it's purely for display purposes across a small number of rows I'm kinda inclined to say keep it in the UI layer of the client but that's driven more by separation of concerns than performance.
Thanks Chris but its an asp.net mvc website with the telerik/kendo grid which renders different than a winforms grid. It calls the controller method I posted to get its datasource at the client levelQuote:
Originally Posted by ChrisE
Thanks Gary, I think you guys are right on the minimal performance hit with Row_Number(). I will test it out once the page is complete and do a comparisonQuote:
Originally Posted by GaryMazzone
Since the "counter" code is in the Controller its processed on the server.Quote:
Originally Posted by FunkyDexter