Month: October 2024
Creating VBA Code to Remove Similar Data Entries in Table
Hi all,
I’m currently working with large data sets where we have 1000s of lines that are equal and opposite to another. For example you can see in the below table;
BATCHNAMEAmountDateDave’s Fund1000008/01/2024Reverses Dave’s Fund-1000020/02/2024Dave’s new Fund500024/02/2024
What I would like to do is have a VBA code that matches the “Dave’s Fund” line and “Reverses Dave’s Fund” line and removes them from the data set (but only if the values add up to nil).
I’ve began on some code that would remove any line that contains the text “Reverses” but I’d like to add in the criteria that removes the original line and checks that the values net to nil.
Sub DeleteRowsOnCriteria()
Dim lastRow As Long, dataRow As Long
Dim ACCOUNTEDBALANCE As Long
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For dataRow = lastRow To 2 Step -1
BATCHNAME = Range(“B” & dataRow).Value
If InStr(1, BATCHNAME, “Reverses”) Then
Rows(dataRow).Delete
End If
Next dataRow
End Sub
Would anyone have any idea on how to add to the code to achieve it’s aims?
Many thanks,
Daniel
Hi all, I’m currently working with large data sets where we have 1000s of lines that are equal and opposite to another. For example you can see in the below table; BATCHNAMEAmountDateDave’s Fund1000008/01/2024Reverses Dave’s Fund-1000020/02/2024Dave’s new Fund500024/02/2024 What I would like to do is have a VBA code that matches the “Dave’s Fund” line and “Reverses Dave’s Fund” line and removes them from the data set (but only if the values add up to nil). I’ve began on some code that would remove any line that contains the text “Reverses” but I’d like to add in the criteria that removes the original line and checks that the values net to nil. Sub DeleteRowsOnCriteria()
Dim lastRow As Long, dataRow As Long
Dim ACCOUNTEDBALANCE As Long
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For dataRow = lastRow To 2 Step -1
BATCHNAME = Range(“B” & dataRow).Value
If InStr(1, BATCHNAME, “Reverses”) Then
Rows(dataRow).Delete
End If
Next dataRow
End Sub Would anyone have any idea on how to add to the code to achieve it’s aims? Many thanks, Daniel Read More
MVP’s Favorite Content: Applied Skills, Sentinel, M365
In this blog series dedicated to Microsoft’s technical articles, we’ll highlight our MVPs’ favorite article along with their personal insights.
Jonah Andersson, Microsoft Azure MVP, Sweden
Browse Credentials – Applied Skills | Microsoft Learn
“Microsoft Learn’s Applied Skills is one of my favorite contents because it gives the opportunity for learners of Microsoft-technologies to learn by doing for free, on-demand, and they are based on real-life project examples.”
*Relevant Activity: I recently did an Ask-Us-Anything session with fellow MVPs and MCTs about the Microsoft Learn’s Applied Skills and the livestream was recorded on my YouTube Channel Microsoft Applied Skills AMA with 3 Microsoft Certified Trainers – YouTube
Elton Bordim, Microsoft Azure MVP, Brazil
Deploy and manage Microsoft Azure Arc–enabled servers – Applied Skills | Microsoft Learn
Deploy and manage Azure Arc-enabled Servers – Training | Microsoft Learn
“This Applied Skills validates the professional’s knowledge with the necessary skills to work with Azure Arc. I recommended to the community in Brazil to dedicate time to this study because a large part of the colleagues work in companies that use hybrid environments.”
*Relevant Blog: Transforme suas habilidades Técnicas com as Applied Skills em Azure Arc – Elton Bordim – MCT | AZ-104 | AZ-305 | SC-200 | AZ-800 e 801
Bill Clarkson-Antill, Security MVP, New Zealand
Azure Sentinel Fusion Detection for Ransomware (microsoft.com)
“This is probably the best representation of how Fusion works within Microsoft Sentinel, leveraging AI-driven correlation to automatically detect multi-stage attacks by analyzing diverse signals across your environment. It truly shows the power of Microsoft Sentinel in conjunction with the rest of the Microsoft Defender family of products and how seamlessly they integrate to provide comprehensive threat protection and enhanced security visibility across your organization.”
Michael Blumenthal, M365 MVP, United States
Message center in the Microsoft 365 admin center – Microsoft 365 admin | Microsoft Learn
“Managing change in your tenant is a key operational activity. Here’s documentation on the Message Center, which is where Microsoft posts all the change announcements for your tenant.”
*Relevant Activities: Tracking what’s new and next for Teams in Your tenant.pptx (slideshare.net) (Filename there is a bit of a misnomer – it’s not just about Teams, it’s about the entire tenant).
MVPs Daniel Glenn and Darrell Webster also have a YT show about Message Center. New Teams Calendar. SharePoint Design Ideas | Ep 353 (youtube.com)
Microsoft Tech Community – Latest Blogs –Read More
Deploying HCX for VM Migration – Part 1
Deploying HCX in your VMware environment
This is part one of a step-by-step guide to deploy HCX in your Azure VMware Solution private datacenter.
In this post, we’re going to take you on a journey through the intricacies of setting up and deploying VMware HCX Enterprise in AVS and on-premises.
Activating HCX Enterprise in Azure VMware Solution
The first step towards deploying HCX begins with logging into your AVS private cloud and heading over to the add-ons section. A few clicks and scrolls later, you’ll spot the option for migration using VMware HCX. It’s here where you’ll agree to the terms and conditions and enable the deployment of VMware HCX Enterprise, a process that takes just a few minutes.
Navigating the HCX Manager ️
Once the deployment is complete, it’s time to log into the HCX Manager using your vCenter credentials from AVS. Here, HCX allows you to download the OVA or copy the link, which you can import into a Content Library. For this demonstration, I show downloading and importing for deployment on-premises
Deploying from an OVF Template
With the OVA downloaded, it’s time to log into our vCenter and deploy from an OVF template. Just upload the file from your desktop and follow the simple steps.
Importing into the Content Library
Another way to approach this is by importing into the Content Library. I go back in and create a Content Library in the on-premises vCenter, assign a meaningful name, and import using the URL provided from the HCX settings in AVS.
Activating On-Premises HCX Connector VM
With the necessary components in place, it’s time to activate our on-premises HCX Connector VM. Grab the key from your AVS private cloud’s HCX settings, give it a name and you’re to activate the VM.
Configuring HCX ️
With the key in hand, paste it into the activation section of the HCX connector. Once activated, restart the appliance, and after about 15 minutes, you should see your vCenter up and running in the HCX Dashboard. Now you’re ready to configure your HCX settings.
Last Steps
After a few more configurations, setting up HCX roles, and waiting for the plugins to show up in vCenter, you’ll see your HCX deployment and configuration finally complete. You’ve successfully downloaded the OVA, imported it, deployed HCX on-prem, and enabled it in the Azure VMware Solution private cloud.
Part 2 will cover we will be creating the Site Pairing to connect the On-Premises HCX appliance to the AVS HCX appliance.
Comment below with any question
Thanks
Amy Colyer
Microsoft Tech Community – Latest Blogs –Read More
Used a template and the “anyone can respond” option is greyed out
I used a template and the “anyone can respond” option is greyed out. The administrator options are set up correctly. I tried copying the form but of course it has the same problem. Is there any way to copy the information to another blank form or changing to response option?
I used a template and the “anyone can respond” option is greyed out. The administrator options are set up correctly. I tried copying the form but of course it has the same problem. Is there any way to copy the information to another blank form or changing to response option? Read More
Hololens 2 nightmare!
The device as far as I know is bricked due to a faulty update released by Microsoft. For some reason the device is not charging above 1 LED light (Even when charging it overnight).
Microsoft customer care suggested to re flash the device which is a simple straight forward process but the device is nowhere the minimum 40% charge because of some restriction.
Getting into EDL mode by pressing the Brightness UP + Brightness DOWN + Power button until the LED’s disappear only enables me to update the driver using the device manager window.
Thinking this as a bypass to the 40% charge requirement, tried connecting to the ARC software with the latest .ffu file and the recovery .ffu file (in case the process got stuck).
ARC opens a page which says unable to connect to the server (which I understand) but will not let me choose the manual package file I have downloaded. The option is greyed out. Tried everything but no luck.
Tried to solve this using the Windows Recovery tool which was used to recover LUMIA phones back in the day, the device does not want to connect to anything.
If anyone has any ideas on solving this, I am all ears!
P.S.
All of this looks suspicious
The device as far as I know is bricked due to a faulty update released by Microsoft. For some reason the device is not charging above 1 LED light (Even when charging it overnight). Microsoft customer care suggested to re flash the device which is a simple straight forward process but the device is nowhere the minimum 40% charge because of some restriction. Getting into EDL mode by pressing the Brightness UP + Brightness DOWN + Power button until the LED’s disappear only enables me to update the driver using the device manager window. Thinking this as a bypass to the 40% charge requirement, tried connecting to the ARC software with the latest .ffu file and the recovery .ffu file (in case the process got stuck). ARC opens a page which says unable to connect to the server (which I understand) but will not let me choose the manual package file I have downloaded. The option is greyed out. Tried everything but no luck. Tried to solve this using the Windows Recovery tool which was used to recover LUMIA phones back in the day, the device does not want to connect to anything. If anyone has any ideas on solving this, I am all ears! P.S.All of this looks suspicious Read More
End User Portal for Defender
Hi guys,
We’d like to introduce a way of allowing line managers and in some cases end users to be more self-sufficient in monitoring, tracking and remediating their own device vulnerabilities.
Is anyone aware a way of delegating some of the information available in the Defender Dashboard to users and teams? Even being able to see an individuals secure score would be a start.
Thanks in advance.
Hi guys, We’d like to introduce a way of allowing line managers and in some cases end users to be more self-sufficient in monitoring, tracking and remediating their own device vulnerabilities. Is anyone aware a way of delegating some of the information available in the Defender Dashboard to users and teams? Even being able to see an individuals secure score would be a start.Thanks in advance. Read More
Line Chart Has Gone Crazy
Hello all,
This is my dataset:
I’m trying to show a trend analysis.
When I have “Tickets Issued” and “Abandoned” shown, it’s cluttered, but it’s ok.
Now pay attention to the y-axis and the data labels. When I add “Total Served”, the axis points doubles and the graph plots way above what it should be. The green and orange line should be much closer, and there should be no point above 4000.
Even when I reduce the vertical axis bounds to 4000, the graph doesn’t change.
Hello all, This is my dataset:I’m trying to show a trend analysis. When I have “Tickets Issued” and “Abandoned” shown, it’s cluttered, but it’s ok. Now pay attention to the y-axis and the data labels. When I add “Total Served”, the axis points doubles and the graph plots way above what it should be. The green and orange line should be much closer, and there should be no point above 4000. Even when I reduce the vertical axis bounds to 4000, the graph doesn’t change. Read More
Fixing a #SPILL! error
My formula sometimes results in a spill error, and I’m unsure why. I have the formula =LET(Num, N2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), Factors) listing the factors of the number in cell N2, and a simple =SQRT(O3#) to show the square root of those factors in the next column. The factor-finding formula always works, but the square root formula results in the spill error when there are too many factors. Why is this and how do I fix it?
My formula sometimes results in a spill error, and I’m unsure why. I have the formula =LET(Num, N2, All, SEQUENCE(Num), Factors, FILTER(All, MOD(Num, All)=0), Factors) listing the factors of the number in cell N2, and a simple =SQRT(O3#) to show the square root of those factors in the next column. The factor-finding formula always works, but the square root formula results in the spill error when there are too many factors. Why is this and how do I fix it? Read More
migrate cpanel mail to office 365
We are a charity that wants to transfer email services with mailings and attachments to Microsoft Group, we hope you will help us in Saudi Arabia as soon as possible. Thank you for your cooperation
We are a charity that wants to transfer email services with mailings and attachments to Microsoft Group, we hope you will help us in Saudi Arabia as soon as possible. Thank you for your cooperation Read More
Removing Screen flicker and adding efficiency to Copy Paste Macro
Hello smart excel users,
I have a macro that I’ve made that pulls data from other reports. I typically get to use .range = .value2 because I use table 99% of the time, however, in the case of my stand alone reports I cannot use tables and I have to use merged cells. because of this, I have not been able to come up with a good macro that removes the need to copy and paste data a bunch of times between workbooks and it causes the screen to flicker like crazy when going back and forth between the two workbooks (disabling screen updating will not work as its two separate windows) and unfortunately I need a bunch of copy pastes instead of just one due to the limitations revolving around copy paste of merged cells and paste area size differences…..
I’m sure there’s a way to get all the data that I require to be pulled over without the extensive back and forth but I have not found a solution yet.
I have attached two sample workbooks. The one which is TEST REPORT has a button that can be clicked. It prompts you to select the source data that I have also attached and should pull the data from the source report.
Please let me know If you have any ideas on how it could run smoother and potentially remove the horrendous screen flickers.
Hello smart excel users, I have a macro that I’ve made that pulls data from other reports. I typically get to use .range = .value2 because I use table 99% of the time, however, in the case of my stand alone reports I cannot use tables and I have to use merged cells. because of this, I have not been able to come up with a good macro that removes the need to copy and paste data a bunch of times between workbooks and it causes the screen to flicker like crazy when going back and forth between the two workbooks (disabling screen updating will not work as its two separate windows) and unfortunately I need a bunch of copy pastes instead of just one due to the limitations revolving around copy paste of merged cells and paste area size differences….. I’m sure there’s a way to get all the data that I require to be pulled over without the extensive back and forth but I have not found a solution yet. I have attached two sample workbooks. The one which is TEST REPORT has a button that can be clicked. It prompts you to select the source data that I have also attached and should pull the data from the source report. Please let me know If you have any ideas on how it could run smoother and potentially remove the horrendous screen flickers. Read More
Creating New Account
I have been unsuccessful in creating a new account in OneDrive because four different times after going through the five-step puzzle solving routine and successfully completing it with the correct answers and was told I had proven that I was a real person, then the same 5-step puzzle solving routine comes back up again as if I had not passed it. I have tried using my other email and by creating a new email – neither of which was successful??? Looks as if Microsoft doesn’t want me as a customer. But any help would be appreciated. B. Butler
I have been unsuccessful in creating a new account in OneDrive because four different times after going through the five-step puzzle solving routine and successfully completing it with the correct answers and was told I had proven that I was a real person, then the same 5-step puzzle solving routine comes back up again as if I had not passed it. I have tried using my other email and by creating a new email – neither of which was successful??? Looks as if Microsoft doesn’t want me as a customer. But any help would be appreciated. B. Butler Read More
华纳公司游戏网址-17300435119（微同）
对目标市场进行深入调研，了解行业趋势、竞争对手情况和客户需求。分析 Azure 上分析服务的特点和优势，确定目标客户群体和市场定位。收集和分析客户反馈和市场数据，为营销决策提供依据。
帮助合作伙伴打造独特的品牌形象，包括品牌名称、标志、口号等。制定品牌推广策略，通过线上线下渠道进行品牌宣传，提高品牌知名度和美誉度。建立品牌口碑管理机制，及时处理客户投诉和建议，维护品牌形象。
策划各种营销活动，如研讨会、培训课程、案例分享会等，吸引潜在客户的关注。组织线上线下活动的执行，包括场地布置、嘉宾邀请、活动流程管理等。活动后进行效果评估和总结，为后续活动提供经验教训。
制定数字营销策略，包括搜索引擎优化（SEO）、搜索引擎营销（SEM）、社交媒体营销、内容营销等。管理合作伙伴的社交媒体账号，发布有价值的内容，与客户进行互动和沟通。利用电子邮件营销、短信营销等方式，向潜在客户推送个性化的营销信息。
为合作伙伴提供销售支持，包括销售培训、销售工具开发、销售数据分析等。管理合作伙伴的销售渠道，包括经销商、代理商、合作伙伴等，确保销售渠道的畅通和高效。协助合作伙伴制定销售激励政策，提高销售团队的积极性和业绩。
合作伙伴营销即服务（Partner Marketing as a Service，PMaaS）在 Azure 的生态系统中具有重要意义。二、服务内容市场调研与分析对目标市场进行深入调研，了解行业趋势、竞争对手情况和客户需求。分析 Azure 上分析服务的特点和优势，确定目标客户群体和市场定位。收集和分析客户反馈和市场数据，为营销决策提供依据。品牌建设与推广帮助合作伙伴打造独特的品牌形象，包括品牌名称、标志、口号等。制定品牌推广策略，通过线上线下渠道进行品牌宣传，提高品牌知名度和美誉度。建立品牌口碑管理机制，及时处理客户投诉和建议，维护品牌形象。活动策划与执行策划各种营销活动，如研讨会、培训课程、案例分享会等，吸引潜在客户的关注。组织线上线下活动的执行，包括场地布置、嘉宾邀请、活动流程管理等。活动后进行效果评估和总结，为后续活动提供经验教训。数字营销制定数字营销策略，包括搜索引擎优化（SEO）、搜索引擎营销（SEM）、社交媒体营销、内容营销等。管理合作伙伴的社交媒体账号，发布有价值的内容，与客户进行互动和沟通。利用电子邮件营销、短信营销等方式，向潜在客户推送个性化的营销信息。销售支持与渠道管理为合作伙伴提供销售支持，包括销售培训、销售工具开发、销售数据分析等。管理合作伙伴的销售渠道，包括经销商、代理商、合作伙伴等，确保销售渠道的畅通和高效。协助合作伙伴制定销售激励政策，提高销售团队的积极性和业绩。 三、优势与价值 Read More
Program crash in all instances
I have a problem with windows server 2019, I have several users logged in remote desktop, occasionally the same program crashes in all instances, it remains blocked for many minutes then without any manual intervention everything returns to working order.
From the error logs I have “windows does not recognize the program xxxxxxx.exe”
Can anyone help me?
Thanks
Marzio
I have a problem with windows server 2019, I have several users logged in remote desktop, occasionally the same program crashes in all instances, it remains blocked for many minutes then without any manual intervention everything returns to working order.From the error logs I have “windows does not recognize the program xxxxxxx.exe”Can anyone help me?ThanksMarzio Read More
Phonetic spelling of names
Is there a way to allow the phonetic spelling of an agent’s name for the ‘say a name’ function in Teams phones? We have a Brittney and a Britanea, pronounced the same (brit-nee), but Teams is looking for Britanea to be pronounced bri-tay-nee-ah.
Is there a way to allow the phonetic spelling of an agent’s name for the ‘say a name’ function in Teams phones? We have a Brittney and a Britanea, pronounced the same (brit-nee), but Teams is looking for Britanea to be pronounced bri-tay-nee-ah. Read More
Mohman Khan Jee Hospital File Open
Mohman Khan Jee File important open illustrator file not supported please registered is update install download fasted 2 panaflex 30 second open file work & final please supported
Mohman Khan Jee File important open illustrator file not supported please registered is update install download fasted 2 panaflex 30 second open file work & final please supported Read More
Range of arrays and mutilple drop down filter options
This is how data is available by months and transcation type
Iam trying to extract some financial data which is available by periods (by months) and transaction type (Rev,GM,SGA,OM) and the user can filter by month or select mutilple periods and data needs to be summarised by transaction type which is already set.The only dynamic part is the period which the user can select .I have already set data validation field with multilple filter options for the period .Which excel formula can i use for the financial data to be extracted by period.I have a tried IF formula but the period combinations are huge and excel formula length is crazy.Is there any simplified way of extracting this information??
This is how data is available by months and transcation typeIam trying to extract some financial data which is available by periods (by months) and transaction type (Rev,GM,SGA,OM) and the user can filter by month or select mutilple periods and data needs to be summarised by transaction type which is already set.The only dynamic part is the period which the user can select .I have already set data validation field with multilple filter options for the period .Which excel formula can i use for the financial data to be extracted by period.I have a tried IF formula but the period combinations are huge and excel formula length is crazy.Is there any simplified way of extracting this information?? Read More
Help on enabling Insert, move or copy worksheet in excel
Good day to all.
In one of my excel files, the sheet tab under the insert main tab is greyed out i.e. remaining disabled.
Cells, column and row tabs are enabled but the sheet tab is disabled.
Upon right click on the sheet tab in the bottom scroll bar the all tabs except view code and protect sheet are disabled.
I tried in the advanced menu in options tab under file but cant figure out to restore the insert sheet , move or copy sheet option.
Request help and guidance from anyone on this.
Good day to all.In one of my excel files, the sheet tab under the insert main tab is greyed out i.e. remaining disabled.Cells, column and row tabs are enabled but the sheet tab is disabled.Upon right click on the sheet tab in the bottom scroll bar the all tabs except view code and protect sheet are disabled.I tried in the advanced menu in options tab under file but cant figure out to restore the insert sheet , move or copy sheet option.Request help and guidance from anyone on this. Read More
New features for Azure Virtual Desktop for Azure Stack HCI
Today, we’re excited to announce new features and capabilities for Azure Virtual Desktop for Azure Stack HCI that will benefit admins responsible for configuring, managing, and optimizing their VDI environments. We’re also announcing that Azure Virtual Desktop for Azure Stack HCI now supports per-user access pricing.
Azure Virtual Desktop for Azure Stack HCI was made generally available in February 2024 with a goal of bringing the security and rich functionality of Microsoft Azure to customers who cannot run virtualization workloads in the cloud or need the benefits of low latency access to cloud functionality. Additionally, Azure Virtual Desktop for Azure Stack HCI was designed to ensure that Azure Virtual Desktop features, including the ability to run Windows 11 and Windows 11 and Windows 10 multisession, work well in hybrid scenarios.
Over the last eight months, customers from multiple industries including healthcare, government, retail, financial services, and education have embraced Azure Virtual Desktop for Azure Stack HCI to help IT admins, customers, and students get the benefits of cloud virtual desktop infrastructure (VDI) while complying with organizational requirements regarding public cloud usage. We’ve had a lot of great feedback, and we’ve used it to inform our innovation roadmap.
New features and capabilities generally available and readily accessible
It’s not only general availability of new features that we’re pleased to announce. We’re also excited to share that these features, which are described below, can already be accessed through the Azure Virtual Desktop for Azure Stack HCI admin experience.
Autoscale for host pools
Autoscale is now generally available for Azure Virtual Desktop host pools on Azure Stack HCI. Autoscale allows you to scale available session hosts up or down according to a schedule to optimize deployment costs. The same scaling plans applied to Azure virtual machines (VMs) can now be applied to Azure Stack HCI VMs.
Start VM on Connect
Start VM on Connect helps reduce costs by enabling users to power on VMs used as session hosts only when they’re needed. You can then power off VMs when they’re not needed. Azure Virtual Desktop for Azure Stack HCI users now have the option to use this feature with Azure Stack HCI VMs.
Windows 11 and Windows 10 Enterprise single-session images on Azure Marketplace
IT admins can now download Windows 11 or Windows 10 Enterprise single-session images from Azure Marketplace to their Azure Stack HCI clusters. By making single- and multisession Windows 11 images available, admins gain additional flexibility in choosing VMs for session hosts.
Azure Marketplace Catalog for Azure Virtual Desktop for Azure Stack HCI
Azure Virtual Desktop Insights support
Azure Virtual Desktop provides a centralized, first-party monitoring solution that enables IT admins to detect, diagnose, and resolve issues within their environments. You can now monitor Azure Virtual Desktop session hosts on Azure Stack HCI using Azure Virtual Desktop Insights.
Screenshot of a sample Azure Virtual Desktop Insights report
Azure Virtual Desktop for Azure Stack HCI supports per-user access pricing
Azure Virtual Desktop for Azure Stack HCI now supports per-user access pricing. Organizations, including independent software vendors (ISVs) could already use Azure Virtual Desktop to provide external users with a Software-as-a-Service (SaaS) experience for software access.
That delivery and per-user access pricing model is now also available to organizations and ISVs that want to provide external users with access to their solutions via Azure Stack HCI. Learn more about pricing and contact your sales rep for more information.
What’s next for Azure Virtual Desktop for Azure Stack HCI?
We’re excited by the enthusiastic response that Azure Virtual Desktop for Azure Stack HCI has received from customers, and we continue to listen closely to customer feedback. We’re committed to a path of continuing innovation for Azure Virtual Desktop for Azure Stack HCI.
If you’re an existing customer, thank you for joining us. If you’re interested in Azure Virtual Desktop for Azure Stack HCI, please contact your local Microsoft sales representative to schedule an information session.
To learn more about these new features and capabilities, please visit our documentation page. And, be sure to regularly visit the Azure Virtual Desktop blog for more about recent Azure Virtual Desktop feature updates.
Stay up to date! Bookmark the Azure Virtual Desktop Tech Community.
Microsoft Tech Community – Latest Blogs –Read More
Lesson Learned #510: Using CProfiler to Analyze Python Call Performance in Support Scenarios
Last week, while working on a support case, our customer was facing performance issues in their Python application.
After some investigation, I decided to suggest CProfiler to identify which function call was taking the most time and use that as a starting point for troubleshooting.
So profiling the Python code became essential to pinpoint the bottleneck. I suggested using CProfiler, a built-in Python module, which helps you profile your code and identify performance issues in real time.
But first, I ran some examples with my own test code to see the results.
In this code snippet:
RunCommandTimeout is a function that performs command timeouts against a SQL database.
cProfile.Profile() starts profiling the code within the context.
After execution, pstats.Stats(Profile) helps us visualize the most time-consuming calls, sorting them by cumulative time.
import cProfile
import pstats
with cProfile.Profile() as Profile:
RunCommandTimeout(initial_timeout=1, loop_count=5, retry=True, retry_count=3, retry_increment=4)
results = pstats.Stats(Profile)
results.strip_dirs().sort_stats(‘cumulative’).print_stats(10)
I was able to track the exact function calls and time spent on each one, for example,
Function calls and their frequency
Time spent in each function (including sub-calls)
Cumulative time taken by functions, which is useful for spotting bottlenecks
Ordered by: cumulative time
List reduced from 181 to 10 due to restriction <10>
ncalls tottime percall cumtime percall filename:lineno(function)
1 0.357 0.357 84.706 84.706 GiveNotesPerformance.py:240(RunCommandTimeout)
7 54.108 7.730 54.108 7.730 {method ‘execute’ of ‘pyodbc.Cursor’ objects}
3 27.001 9.000 27.001 9.000 {built-in method time.sleep}
4 0.001 0.000 3.025 0.756 GiveNotesPerformance.py:63(ConnectToTheDB)
4 2.945 0.736 2.951 0.738 {built-in method pyodbc.connect}
1 0.209 0.209 0.209 0.209 {method ‘close’ of ‘pyodbc.Connection’ objects}
29 0.035 0.001 0.035 0.001 {method ‘flush’ of ‘_io.TextIOWrapper’ objects}
12 0.000 0.000 0.032 0.003 GiveNotesPerformance.py:526(set_text_color)
4 0.001 0.000 0.030 0.007 GiveNotesPerformance.py:39(get_credentials_from_file)
4 0.027 0.007 0.027 0.007 {built-in method io.open}
Analyzing the profiling results for the Python code execution, sorted by cumulative time, I was able to find the following results:
I found very interesting points, such as the execute method being called 7 times with a total time of 54 seconds. This indicates that the execute method is responsible for the majority of the time spent in this execution. Each call takes an average of 7.73 seconds, suggesting that the database queries are the primary bottleneck.
Additionally, my time.sleep function shows that it was called to introduce delays, likely as part of the retry mechanism built into the RunCommandTimeout function. Each sleep call lasted for an average of 9 seconds.
Enjoy!
Microsoft Tech Community – Latest Blogs –Read More
