![]() ![]() If you like, you can use an approach like this that doesn't hit any "real" tables at all, which should perform just as fast or faster. It could be any table with many rows-the tables in the FROM clause are just being cross-joined together to get their Cartesian Product (meaning a LOT of rows) so that the Row_Number() function has something to work with to create a rowset with numbers from 1 to 1,000,000. It has value because it is guaranteed to have a lot of rows. The syscolumns table is itself meaningless. When you instead treat T-SQL as a procedural language and loop over each item one at a time, it can't use those optimizations and has to execute each statement one at a time under a completely different kind of execution context. Inside the engine, of course it has to loop over each row, but it is heavily optimized. ![]() The reason it is fast is because SQL Server is optimized for "row-based" operations. To insert to a table requires at least INSERT or SELECT. You can know this because all the system tables are in the FROM clause. It is merely using the syscolumns table as a means to get a lot of rows. Where RW between 1 and (convert(integer, - convert(integer, not "generating MAC addresses into a system table" as you say. Select convert(BINARY(8),RW + convert(integer, row_number() over(order by a.id) As RW from syscolumns,syscolumns a,syscolumns b ,syscolumns c) b -I do not understand this line very well. ![]() select convert(integer,+ - convert(integer,+ = 983220 However it is generating MAC addresses into a system table: DECLARE BINARY(8) Select convert(BINARY(6),(convert(bigint, (SELECT IDENT_CURRENT('Mac_Address'))) + convert(bigint, is the code I found online to do this, and it executes in a few seconds. WHILE(convert(bigint, (SELECT IDENT_CURRENT('Mac_Address'))) < (convert(bigint, - convert(bigint, into Mac_Address (MacAddress) select convert(bigint,+ - convert(bigint,+ = 1048575 Here is what I am doing, however it is taking ~10 minutes to generate a million MAC addresses and insert them into my table: DECLARE BINARY(6) Is there a way to generate a ~million MAC addresses more efficiently? ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |