The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,825 other followers

Archive for November 28th, 2019

sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow

Posted by jpluimers on 2019/11/28

[WayBack] sql server – I need a slow query on AdventureWorks (SQL 2005) – Stack Overflow:

Medium slow:

SELECT * FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p ON s.ProductID = p.ProductID

Very slow:

SELECT * FROM Production.TransactionHistory th
INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity

It is based on the output of [WayBack] sql server – Query to list number of records in each table in a database – Stack Overflow, which originally ommited tables starting with dt (as those were be used for the “Database Diagram” in the SQL Server 7/2000 era using tables like dtProperties), but which I adopted using:

The query below does not support SQL Server 2000, where you would have to use things like objectproperty, but since by now even the [WayBack] documentation has been retired on the Microsoft site, you need to read [WayBack] Get list of tables but not include system tables (SQL Server 2K)? – Stack Overflow

In the end, it filters out tables like dbo.sysdiagrams that are generated by SQL Server Management Studio (SSMS), which are SSMS System Tables, but technically not SQL Server System Tables.

select
    s.name as SchemaName,
    t.name as TableName,
    i.name as indexName,
    p.rows,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
from 
    sys.tables t
inner join
    sys.schemas s on t.schema_id = s.schema_id
inner join
    sys.indexes i on t.object_id = i.object_id
inner join
    sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join
    sys.allocation_units a on p.partition_id = a.container_id
where
    t.is_ms_shipped = 0 -- not internal to SQL Server
    and
    (not exists (
        select ep.major_id
        from sys.extended_properties ep
        where 
            ep.major_id = t.object_id and
            ep.minor_id = 0 and
            ep.class = 1 and
            ep.name = N'microsoft_database_tools_support'
        )
    ) -- not internal to SQL Server Management Studio
    and
    i.index_id <= 1
group by
    s.name, t.name, i.object_id, i.index_id, i.name, p.rows
order by
    s.name, t.name

The counts for the [WayBack] GitHub version of AdventureWorks is this:

Read the rest of this entry »

Posted in Database Development, Development, Software Development, SQL Server | Leave a Comment »

delphi – Spring4D: Why is list of type TObjectList freed automatically after iteration? – Stack Overflow

Posted by jpluimers on 2019/11/28

A nice question and even nicer answer at [WayBack] delphi – Why is list of type TObjectList freed automatically after iteration? – Stack Overflow.

It comes down to the Spring4D collection classes expecting to be accessed using interface references because they descend from TInterfacedObject which also favours interface references over object references.

If you access them solely using object references, they start out with a reference count of 0 (zero). If an operation then first increases that to 1 (one), then decreases it back to 0 (zero), the collection instance gets freed.

A few nice tips and (sometimes opposing) opinions from the question/answer thread and the referencing G+ thread [WayBack] What do you think, +Stefan Glienke? ##Spring4D – Agustin Ortu – Google+ make them well worth reading.

Some:

  • Instead of TObjectList<TFuu>.Create, you should use TCollections.CreateObjectList<TFuu>.
    This way, you only need Spring.Collections in your uses clause
  • interface-only usage should be enforced by hiding the classes in the implementation, exposing only interfaces
  • The functions of TCollections only return interface references and – more importantly – allow for code folding starting with Spring4D 1.2.
  • Hiding the classes in an implementation part is not possible because then this would break any possibility to inherit from these classes and extend them (as I know people do).
  • Never use object references to classes that inherit from TInterfacedObject because the reference counting can kick in and destroy your instance anywhere (or manually call _AddRef/_Release).
  • if you want to have classes, then use System.Generics.Collections

–jeroen

Posted in Conference Topics, Conferences, Delphi, Development, Event, Software Development | Leave a Comment »

 
%d bloggers like this: