1. The Key ORM problem

    After working with and writing various forms of ORM for years, I think I have finally pinned down my core issue with them. Inspired by Rich Hickey’s talk from StrangeLoop 2011: http://bit.ly/1eGEUkk.

    ORMs do not focus on Outgoing data transformations.

    It’s not that ORMs aren’t useful for reading data. It’s simply that they suck for modifying data.

    ORMs bill themselves as tools for providing mappings between “Objects” and “Data”. When it comes to reading data, thinks like Entity Framework (EF) are useful for combining data and returning it a structured fashion. It can easily map “Data” into “Objects”.

    However, where tools like EF fall down are when it comes to modifying data. The standard method of changing data involves: - Loading the data into an object - Making some changes to the in-memory representation - Calling Save()

    But this process never actually works that way. - It doesn’t handle concurrent changes or over-writing each other’s data. - It never maps to “atomic” changes like "increment this number" or "add this to a sub-array in the data".

    When you get started it’s “easy”, but it inevitably tumbles into the underlying complexity that it’s trying to hide from you. You inevitably start writing stored procedures or hand-coding SQL or other DB transformations.

    I think the core reason for this is the ORM view of the world. ORM literally "maps data into objects" and that’s great for reading. And it’s OK for creating data. But when I want to update data I really want to do is map a transformation onto data.

    ORMs don’t do this. They track changes on an object (at best) and then attempt to map the object changes to data changes and then map those into DB calls.

    But that’s never what I want to do. I want to allow specific transformations and map those transformations directly onto the data in a way that’s rational for that data. I don’t want people messing with the individual columns of my data. That’s a recipe for disaster.

    So that’s my key ORM problem, it sucks for modifying data.

    7 months ago  /  0 notes

  2. Counting Outgoing TCP connections by Port

    Here’s the PowerShell script. Great for identifying the types of connections being held.

    [Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties().GetActiveTcpConnections() |
        where { $_.State -eq "Established" } |
        foreach { $_.RemoteEndPoint } | 
        group -Property Port | 
        sort -Descending -Property Count
    

    1 year ago  /  0 notes

  3. SQL Server, add a random time to a DATETIME column

    Seems simple, the but NEWID() is a great trick.

    dateadd(millisecond, ABS(CHECKSUM(NEWID()))%86400000, dateToIncrement)
    

    The third part is your column name that you want to increment.

    Pulled and modified from here.

    1 year ago  /  0 notes

  4. Group By Week

    When ranging over data, it’s nice to group out data by week of the year. But you also want to display the first day of that week.

    Here it is for SQL Server.

    The key parts are

    GROUP BY DATEPART(YEAR,trans_date),DATEPART(wk,trans_date)
    

    and

    MIN(DATEADD(wk, DATEDIFF(wk,0,trans_date), 0)) AS 'Week date',
    

    1 year ago  /  0 notes

  5. world-shaker:

This is amazing.

    world-shaker:

    This is amazing.

    (via world-shaker-deactivated2013092)

    1 year ago  /  1,323 notes

  6. SQL Server finding slow queries

    Lifted from here:

    SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
    qs.execution_count, 
    qs.total_logical_reads, qs.last_logical_reads,
    qs.min_logical_reads, qs.max_logical_reads,
    qs.total_elapsed_time, qs.last_elapsed_time,
    qs.min_elapsed_time, qs.max_elapsed_time,
    qs.last_execution_time,
    qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qt.encrypted=0
    ORDER BY qs.total_logical_reads DESC
    

    Note that this can be tweaked by sorting on different things such as total_logical_reads / execution_count.

    1 year ago  /  0 notes

  7. Sorting out folder sizes with PowerShell

    https://gist.github.com/3138244

    2 years ago  /  0 notes