Pushpinder Kaur

Yes, of course, hi. My name is Pushpindarkar, and I have 7 years of experience in software development. In my last organization, I was working as a senior software developer, and my role was as a senior developer. In my last organization, I had a role as a senior developer, in which I had responsibility for developing and maintaining the database server, as well as implementing and collaborating with the IT team to deploy scripts into the deployment server. I have completed my bachelor's degree in computer science from Mumbai University, and I also have completed my master's degree in project management. I am very passionate about coding, which is why I pursued a career in software development. Additionally, in my last organization, I had additional responsibilities, including being a scrum master for the development team. I bought training for stakeholders on our new, newly built system, as well as providing training to junior team members. My responsibility was also to maintain technical specification documents and system architecture documents. In the absence of a team leader or manager, I would review code for junior team members. Thank you so much.

When I have to optimize our database performance, I need to take care that all the databases should be running properly without any block or blockage or any portal next because it is very important for us to see that our database should be running in a proper way so that it won't impact our productivity as well as performance. And to overcome this, I usually maintain the database by checking the database using many techniques. Like, I need to take care that there won't be any select star statement in our database. There should be a limit in select star operation so that there won't be any huge load in our database as well as for doing the searching, we can use an index so that we can retrieve the data as soon as possible, like a fast retrieval of the data. Another way to optimize the database performance is to use the WHERE clause so that we can get specific data in a particular range. And for the optimization purpose, we can make sure that we separate databases as per their requirements. For example, in my previous organization, I was working with four countries, but initially, all the four countries' databases were merged into one database. So to optimize the database performance, I divided the main database into full databases as per their countries. And by this, the load on one database was reduced, and we have increased our performance by 30% because there was no workload or data load in our database because of the country. So whenever we use a database for one country, there won't be an impact on the other countries. By using all these techniques, I have optimized the database performance in my last organization. And as a result, we have increased our database performance by 25%, and even the load on data will reduce by 35%. Thank you.

To prevent SQL injection attacks in a store procedure, we usually try to ensure that all the data we are getting is encrypted. For example, in my last organization, I was working on a financial client project in which we had to maintain the username and password for the user. So because it's a financial project, it's very important to secure the data of that particular client or user. So for that, we usually use the SALT technique in which we encrypt our user's password, and it will be stored in our database. And for encryption of that particular password, we need to use decryption of the password. So by this way, we have implemented protection against SQL injection attacks in store procedure by using different techniques.

What is "mhmm."? For using indexes to improve query performance, I usually use the cluster index, as well as a unique index because cluster index is used when we require it, or a cluster index is used because the physical orders will be the same as the table so that we can easily retrieve the data as faster. And even if we want unique data from the database, for that purpose, we are using the unique index. So in my last organization, when I was assigned with the task for query optimization, as we performed query optimizations on that time, I utilized the cluster index so that we can retrieve the data because we need to fetch a record from a particular column. So for using that, the cluster index is most useful for us. And even for getting the uniqueness in a particular column, I have used the unique index so that we won't get any duplicacy across in our database. And so, yes, for using indexes to optimize query performance, I use the clustered and unique index.

Yes. For managing the school server database transaction, I use the asset approach because it is the most popular approach for this purpose. I remember that in my previous organization, Atos, where I was working on a project with an insurance client, and as an insurance client, we had to maintain the data so that it wouldn't impact users' data. So for this purpose, we used the asset transaction. In asset transaction, we ensure that if a transaction occurs, it should be complete; otherwise, it should not be complete. It should be the same as the previous one. I also ensured that if there's any interruption between tables during the transaction, the data impacted due to any issue should be rolled back to the same state so that it wouldn't affect the new entry. To achieve this, we used the asset approach. The asset approach is mainly used for transactions, like I used it for insurance clients and financial clients. We had a client requirement to use the asset transaction. Sorry. Exit concepts, and we know that there are financial outcomings and ingoings in insurance and finance life insurance and finance applications. We had to ensure that all transactions done before and after should be saved. If there's any interruption between transactions, it should be rolled back. It should be committed and rolled back to the original database table so that it wouldn't impact other tables. If all data has been transferred to new tables, we need to check that all additional data in the previous and new tables are the same.

For making sure that there should be a minimized effect of frequency changes. I will make sure that if there's any frequent changes are going on, for example, insertion or updation if I'm doing any entry or if I'm taking something. I will ask if I'm doing some operations. So I will make sure that there should be a trigger. We can say a stored procedure behind that so that there won't be any changes and we can maintain our quality of database. For example, I know that during the click, there will be any insertion or there will be any updation or deletion. So I can create a stored procedure in which I can write a query related to the insert state by gathering all the data which is needed in the insert statement, and I will trigger that thing. And so that there won't be any changes on our tables or database or we don't need to write a query again and again. So I will just call that stored procedure, and the insertion will be done as well as bypassing the arguments, I can do that. And even I can use a view because a view wants to store or take a lot of sizing of a database. And it is very easy to maintain a view. Also, in the view, it is like a temporary table for us in which we can gather the data from one or more tables, and we can store the data there. So by using this way, we can minimize the effect of frequent schema changes, and it won't be taking a lot of space as well as it looks like we are reusing a query.

Yes. To optimize this particular T-SQL function, we can do one that instead of using wear gloves, we can use having clothes and buy, also, we can use a group bike cloth so that all the details will be aligned into one column, and it will be very easy to sort the data, to group that data. And, it will be like a faster process if we are using the group by, it will group all the details as per the column. And by this way, we can easily gather the data.

In this, we need to use a bracket before the case statement so that all the statements, all the case functions will be performed with CMS, as we are required to because if we execute this functionality without it, then it will give a wrong answer. And even we need to use a semicolon at the end of the expression.

For maintaining the database concurrency control in high transaction environments, I will use the isolation strategy, so that there will be a concurrency between the transactions. And if there's any bottleneck or if there's any interruption between transactions, so that it can be rolled back. And, also, I will make sure that all the transactions which are done properly without any interruption, that should reflect into the new table. Or you can see it in another table, and all the entries should be missing from the original table.

Yes. There are many times in which I have to refactor database design for improving the performance. Big in my last organization, when I was working with the client stakeholders, and the manager, we had a collaboration between us. I have collaborated with them and proposed a new solution because in our one database, we were using different sub databases. We were using sub databases for our as compared to, as per the countries, we were using sub tables in our main database. But after that, I realized that it would load the main database. So, after discussion, we proposed one solution for them in which we had to refactor our database design. So that was to divide our main database into sub databases as per the countries. And we performed the operation as per the country-wise. So there wouldn't be any bottleneck, and even there wouldn't be any values that could impact other countries or other users because there could be cases where one user would have a different relationship with many countries. So to overcome that, we used a primary key, foreign key, and composite key so that there wouldn't be any impact with the other table present in the database. And for this purpose, we had to refactor our database design, and we proposed that design to the stakeholders and the higher authority. After getting approval from them, we took the help from the IT operation team, and we deployed all the necessary things that were needed so that it could be useful for future reference. Even for that, we maintained our technical specification document and system architecture documents so that it could be used for future reference.

For documenting my SQL code and database design, I usually use a technical specification document and the system architecture document in which I maintain our SQL database design and SQL code. And as well as, we have two types of approach. Like, first one is a soft copy as well as a hard copy approach. In both soft copy and hard copy approach, we maintain both documents and deploy our soft copy to the deployment server so that the testing team or other stakeholders know about our scripting: what we have done, what we have changed, and what is the future approach for that? This serves as a reference for future reference, so we know about those things. And even in our absence or in case of a company switch, there can be a case where another employee needs to make changes to our database. They can get help from our documentation. So this way, we have done the documentation for SQL code and database design. We keep our hard copy because there may be cases where we can't access our digital documentation and need to discuss a particular thing with stakeholders. It is better to have a hard copy to give decisions or suggestions to stakeholders.