Help with some complex operations

22

That would also mean that I have to take the time to create some voluminous test data. It's your turn to do that. :smiley:

Also, apologies for the snap but I've been getting snapped at a whole lot and thought I'd start returning the "favor". :wink:

One of the things I'm thinking about in the code is that it's a well proven fact that "pre-aggregation" can add a whole lot of performance to code and that's where I was headed with my recommendation.

23

@JeffModen

You don't need "voluminous" test data. The few rows @yosiasz posted will do. Add a few more if you like/need to demonstrate other data pattern(s) that are important. I'm 100% not interested in generating data myself. (Edit: added "myself" for clarity. I'm not opposed to someone using lots of data, I just don't have any interest in manufacturing that data myself.)

24

Not true. Performance is a very, very close second to accuracy.

25 
declare @sqlfor table(col1 char(1), col2 int, col3 int)

insert into @sqlfor
select top  100000 upper(left(name,1)),  column_id, system_type_id
  from sys.all_columns

select * From @sqlfor
26

Thanks for posting the data generation script, Tito but I was hoping Scott would finally do one. Heh... he really has some sort of dislike for doing it.

Shifting gears a bit, not only a nice job on leveraging sys.all_columns as a row source but also nice job using the data within it to match the given problem. The only problem is that it doesn't typically generate 100,000 rows without a cross join to itself. On my 2017 Developer's edition in a new database, it only generates a bit under 10,000 rows.

Since you were kind enough to "provide the seed", I modified it a bit to support a larger test and here's the result. Thanks again for providing the seed code.

   DROP TABLE IF EXISTS #SQLFor;
 CREATE table #SQLFor 
        (Col1 CHAR(3), Col2 INT, Col3 INT, Col4 CHAR(2))
;
 INSERT INTO #SQLFor WITH (TABLOCK)
 SELECT TOP 1000000 
         Col1 = UPPER(LEFT(ac2.name,3))
        ,Col2 = ac2.column_id
        ,Col3 = ac2.system_type_id
        ,Col4 = right(ac2.name,2)
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
;
1 Like
27

Cool, I was trying to base it on one you yourseld had provided a while back but couldn't find it.

I think you @JeffModen and @ScottPletcher both bring a lot to the table, so please lay off with the back and forth. Lets end 2020 on a good note. Technlogoy comes and goes, but people will always be around. I know I learn a lot of technical gems from you both

28

Yes. You're correct and thank you for the reminder, Tito. I'll be back tonight with some (what I think) are some interesting offshoots for problem #2.

29

I disagree with Jeff's basic premise. Not every query needs to be run against 1M rows. And the opportunity cost of doing so is very damaging to the overall performance of the instance. I.e., there are almost always more productive things people could be doing.

We don't know this person's data. We don't even know if there is real data. This could be just a theoretical q. I have limited time available. Why spend that time on something that is likely 100% not relevant to the OP?

1 Like
30

And the OP has never come back to let us know :laughing: what he wants and what his data looks like :+1:

31

Would still love to see your other approaches / offshoots on #2.

32

@JeffModen

Still no code available?

33

Oh, yeah... I have code but you wouldn't be interested, Scott. You can't see testing things against a million rows. :wink:

34

I'm interested in the code itself, the "interesting offshoots for #2". Others might be very interested in the 1M rows part of it.