SilhouetteBS wrote:Does anyone know the SQL query that is run against the database to create the Excel file?
It's not a SQL query. That's why it's so slow. It loops through a list of products, and then queries for their variants.
foreach (var p in products)
{
var productVariants = _productService.GetProductVariantsByProductId(p.Id, true);
foreach (var pv in productVariants)
Worse, it then has to query for list of mapped Categories, Manufacturers, and Pictures. (in the inner pv loop no less, even though those entities are on the product). And in 2.70 and later, worse, it needs to get localized SEO Name
So, if you don't need the latter, then you can run the following in SSMS, and then paste into Excel (set cell format of entire sheet to Text).
Caveat: "NULL" will appear where the real export shows blank, and 1 or 0 will show instead of TRUE or FALSE. You can use ISNULL() or DECODE() to adjust as needed.
select
p.Name,
ShortDescription,
FullDescription,
ProductTemplateId,
ShowOnHomePage,
MetaKeywords,
MetaDescription,
MetaTitle,
--SeName,
AllowCustomerReviews,
p.Published,
pv.Name as ProductVariantName,
SKU,
ManufacturerPartNumber,
Gtin,
IsGiftCard,
GiftCardTypeId,
RequireOtherProducts,
RequiredProductVariantIds,
AutomaticallyAddRequiredProductVariants,
IsDownload,
DownloadId,
UnlimitedDownloads,
MaxNumberOfDownloads,
DownloadActivationTypeId,
HasSampleDownload,
SampleDownloadId,
HasUserAgreement,
UserAgreementText,
IsRecurring,
RecurringCycleLength,
RecurringCyclePeriodId,
RecurringTotalCycles,
IsShipEnabled,
IsFreeShipping,
AdditionalShippingCharge,
IsTaxExempt,
TaxCategoryId,
ManageInventoryMethodId,
StockQuantity,
DisplayStockAvailability,
DisplayStockQuantity,
MinStockQuantity,
LowStockActivityId,
NotifyAdminForQuantityBelow,
BackorderModeId,
AllowBackInStockSubscriptions,
OrderMinimumQuantity,
OrderMaximumQuantity,
AllowedQuantities,
DisableBuyButton,
DisableWishlistButton,
CallForPrice,
Price,
OldPrice,
ProductCost,
SpecialPrice,
SpecialPriceStartDateTimeUtc,
SpecialPriceEndDateTimeUtc,
CustomerEntersPrice,
MinimumCustomerEnteredPrice,
MaximumCustomerEnteredPrice,
Weight,
Length,
Width,
Height,
pv.CreatedOnUtc
--CategoryIds,
--ManufacturerIds,
--Picture1,
--Picture2,
--Picture3,
from ProductVariant pv
join Product p
on p.Id = pv.ProductId
where pv.Deleted = 0
and p.Deleted = 0
joebloe wrote:If you are exporting a List<T> this is a little extension method ...
Nice! But why change CR/LF's to space. CSV would not do that. Just quotes around text - same as for comma.