dcsimg
Results 1 to 11 of 11

Thread: Simple Row Counter SQL?

  1. #1

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,608

    Simple Row Counter SQL?

    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?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,069

    Re: Simple Row Counter SQL?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,608

    Re: Simple Row Counter SQL?

    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?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,069

    Re: Simple Row Counter SQL?

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,608

    Re: Simple Row Counter SQL?

    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;
            }
    Attached Images Attached Images  
    Last edited by RobDog888; Feb 8th, 2019 at 05:08 PM. Reason: Spelling fix
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

  6. #6
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,637

    Re: Simple Row Counter SQL?

    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

    Code:
    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
    HTH
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,274

    Re: Simple Row Counter SQL?

    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,082

    Re: Simple Row Counter SQL?

    Looks like row number may be more impactful on resources than using a counter variable in c# I assume?
    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.

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,608

    Re: Simple Row Counter SQL?

    Quote Originally Posted by ChrisE View Post
    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

    Code:
    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
    HTH
    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 level
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

  10. #10

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,608

    Re: Simple Row Counter SQL?

    Quote Originally Posted by GaryMazzone View Post
    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
    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 comparison
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

  11. #11

    Thread Starter
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,608

    Re: Simple Row Counter SQL?

    Quote Originally Posted by FunkyDexter View Post
    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.

    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.
    Since the "counter" code is in the Controller its processed on the server.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Corsair H100i v2 water cooler, Geforce GTX1060, Samsung M.2 500 GB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2010, VS 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width