July 28, 2020 at 7:43 am
Hello everyone,
I am finding issue while running this query .
WITH XMLNAMESPACES (DEFAULT 'http://example.Schema.Example.data')
Select(Select
* from [dbo].[udfReturnexampleRecords] ()
for xml path('Test'),root('example')
) as XMLTEXT
Here udfReturnexampleRecords is a table valued function and while running complete query , it runs very slow for 20 lakh records.
Any advice please .
Thank you
July 29, 2020 at 3:52 am
Without a fair amount of sample data, replicating your problem might be difficult because 20 lakh records is a ton of data. We at least need a better idea of what your table valued function code is, and some sample data to take a look at what it's doing. XML processing is expensive, and on large quantities of it, it's not likely to perform well. The question I have, is why would you need to process 20 lakh rows of this stuff? Maybe you can split up the processing to limit the number of rows being processed in any one execution, and then somehow stitch the results together (perhaps inserting into the same table on each execution?)...
Please provide more detail...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 29, 2020 at 6:54 pm
Thank You Steve,
Source table : No of records : 20 lakh and may increase, no of columns are 55 and it may also increase.
Requirement is : We need to convert these records into XML and save it in a file, to perform this operation i am using above query.
Function [dbo].[udfReturnexampleRecords] is having Select Query which selects entire column from source table and there is no where clause in the function.
but while running this query from SSIS , its giving error , Like memory out of Exception.
Please suggest.
July 29, 2020 at 7:47 pm
Okay... I know that the term "lakh" is a large number, but let's go ahead and have you state exactly how many 2o of those is in English units, meaning millions, billions, or trillions, or whatever. Also, please say exactly what "very slow" means in terms of actual elapsed time in hours/minutes/seconds. My guess is that when we multiply 55 columns times 20 lakh records, the number of data elements is going to be rather huge, which is why I question the need to do this. That's going to be one heck of a lot of slow string processing, and I'd need to be convinced that the XML format has enough fiscal value to be worth the effort. You may well need to limit the number of rows processed in one execution by, in some way, dividing up the rows into segments that can each be processed separately. This may well take a fair amount of testing and experimentation to determine how many rows you can handle in one go. Also, how large are the values in these 55 columns? Please tell me that there aren't any varchar(max) or nvarchar(max) columns involved... as that might mean that the memory estimates will be 2 GIG per row per such column, and that could easily require a terabyte of ram for a relatively smaller number of rows...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 29, 2020 at 9:32 pm
1 lakh = 100,000
July 29, 2020 at 9:39 pm
What is the purpose of converting all rows in that table to an XML format - with all 55 columns? If this is for an export file - there are other methods for generating a file that might be faster...but then again, any other file format would be better than putting millions of rows of data into a file in an XML format.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2020 at 9:40 am
Hi Steve,
all columns have varchar(100) datatype , we are not using any varchar(max) column or nvarchar(max) columns.
"very slow" query is running for more than 2 hours and then package gets failed with memory out of exception.
As per business requirement , output file should be in xml format and must be saved in ftp server.
July 30, 2020 at 9:46 am
Hi Jeffrey,
we are working in insurance domain and we need to save data in xml file in ftp server , it is a business requirement.
and Yes 1Lakh records = 100000 records.
if you can suggest me how we can process data in batch , but end result should be an xml files which contains all the records.
Thank you for your time and suggestions.
July 30, 2020 at 7:26 pm
Hi Jeffrey,
we are working in insurance domain and we need to save data in xml file in ftp server , it is a business requirement.
and Yes 1Lakh records = 100000 records.
if you can suggest me how we can process data in batch , but end result should be an xml files which contains all the records.
Thank you for your time and suggestions.
I would recommend using something like SSIS to output to an XML file. Doing this all in SQL will be a lot harder than extracting just the data from SQL and having SSIS output that data to a file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 30, 2020 at 10:44 pm
Okay, so we're talking about 2,000,000 rows of 55 columns of up to 100 characters each, plus all the element overhead. This could easily be a full Gigabyte of RAM (or perhaps considerably more), that the XML would occupy during the process, regardless of whether it's SQL or SSIS. Is there that much spare RAM capacity on that server? Or are you trying to return that rowset to a client computer that might not have enough RAM to be able hold it in memory? What's the total size in bytes of the table that supplies the data?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 4, 2020 at 12:58 pm
Hello Steve,
Apologies for being late in reply, we had a discussion with client and they don't want to increase the RAM size ,
Hence i have changed the script , used offset and fetch operators with max of 100000 records and issue is resolved.
I really like to Thank all of you who helped and supported with your inputs.
August 4, 2020 at 2:34 pm
Okay, you've solved the problem (and I'm glad we were able to help) by breaking it up into pieces, but it does kind of leave us hanging as to the question of what the real driver is behind using the XML format. Just saying that it's a "business requirement" doesn't actually answer that question. I'm rather a skeptic that there's any actual useful purpose for that much data in XML format, that couldn't be re-designed to use a more practical format. Also, did you create some process to stitch together the pieces, or did the requirements get changed to allow separate pieces of XML to be used? And your last post leads to the question "why NOT get more RAM?", as one might wonder just how much RAM a server has where a spare GIGabyte is too much to ask for... Although using that much RAM resource is not a great idea, it's just that having so little RAM that a gigabyte isn't readily available could foreshadow a future resource shortage...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply